Using CTE (Common Table Expression)

To create a collection of rows without querying a table or a repeating series of INSERT statements.

declare @seed int
select @seed = 8;

WITH RecurseTable(Id)
AS
(
SELECT @seed

UNION ALL

SELECT R.Id -1
FROM RecurseTable R
WHERE R.Id > 1
)

select *
from RecurseTable

Querying Table Definitions

A query to return metadata about a table. This is useful for dynamic query building like in a SqlAdapter, for example.

SELECT C.COLUMN_NAME,
       C.DATA_TYPE,
       C.CHARACTER_MAXIMUM_LENGTH,
       CASE WHEN PK.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END IS_KEY,
       CASE WHEN IC.object_id IS NULL THEN 0 ELSE 1 END IS_IDENTITY
FROM INFORMATION_SCHEMA.COLUMNS C
       LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK ON C.TABLE_NAME = PK.TABLE_NAME
                                      AND C.COLUMN_NAME = PK.COLUMN_NAME
       INNER JOIN
sys.tables T ON C.TABLE_NAME = T.name
            AND T.[type] = 'U'
       LEFT OUTER JOIN
sys.identity_columns IC ON T.object_id = IC.object_id
                       AND IC.[name] = C.COLUMN_NAME
WHERE C.TABLE_NAME = @TableName