List Columns on Table

More ronseal pages here, this time it's a script to show information on a specific table. This can be sueful when you're getting to know a new database and for copy/pasting out database information for say.. a SQL generating spreadsheet for example:

DECLARE @TableName AS VARCHAR(250)
SET @TableName = 'Persons'
 
SELECT  o.name TableName
, c.name ColumnName
, max_length
, c1.DATA_TYPE
FROM sys.objects o
JOIN sys.columns c ON c.object_id = o.object_id
                    AND o.is_ms_shipped <> 1
JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name
                   AND c1.COLUMN_NAME = c.name
WHERE
--o.name like '%' + @TableName + '%'
o.name = @TableName

DECLARE @TableName AS VARCHAR(250)

SET @TableName = 'Persons'

SELECT  o.name TableName

, c.name ColumnName

, max_length

, c1.DATA_TYPE

FROM sys.objects o

JOIN sys.columns c ON c.object_id = o.object_id

                    AND o.is_ms_shipped <> 1

JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name

                   AND c1.COLUMN_NAME = c.name

WHERE

--o.name like '%' + @TableName + '%'

o.name = @TableName

 If you're interested in getting more information out (for example you're interested in the precision or length of a datatype) then you might be interested in the below slightly extended version:

-- ListColsFull
DECLARE @TableName AS VARCHAR(250)
SET @TableName = 'workpackage'

SELECT  c1.TABLE_SCHEMA 'Schema'
, o.name TableName
, c.name ColumnName
--, c1.ORDINAL_POSITION pos
, max_length
, c1.DATA_TYPE , c1.is_nullable 'Nullable', c1.CHARACTER_MAXIMUM_LENGTH 'CharMaxLen'
, c1.NUMERIC_SCALE 'NumScale', c1.NUMERIC_PRECISION 'NumPrecis', c1.DATETIME_PRECISION 'DtPrecis', c2.CONSTRAINT_NAME 
FROM sys.objects o
join sys.schemas sc on sc.schema_id = o.schema_id
JOIN sys.columns c ON c.object_id = o.object_id
                                  AND o.is_ms_shipped <> 1
JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name
                              AND c1.COLUMN_NAME = c.name
                               and c1.TABLE_SCHEMA = sc.name 
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c2 on c2.TABLE_NAME = o.name                                                                                                                      and c2.CONSTRAINT_SCHEMA = c1.TABLE_SCHEMA                             
WHERE o.name = @TableName
order by c1.TABLE_SCHEMA, o.name, c1.ORDINAL_POSITION

-- ListColsFull

DECLARE @TableName AS VARCHAR(250)

SET @TableName = 'workpackage'

​

SELECT  c1.TABLE_SCHEMA 'Schema'

, o.name TableName

, c.name ColumnName

--, c1.ORDINAL_POSITION pos

, max_length

, c1.DATA_TYPE , c1.is_nullable 'Nullable', c1.CHARACTER_MAXIMUM_LENGTH 'CharMaxLen'

, c1.NUMERIC_SCALE 'NumScale', c1.NUMERIC_PRECISION 'NumPrecis', c1.DATETIME_PRECISION 'DtPrecis', c2.CONSTRAINT_NAME 

FROM sys.objects o

join sys.schemas sc on sc.schema_id = o.schema_id

JOIN sys.columns c ON c.object_id = o.object_id

                                  AND o.is_ms_shipped <> 1

JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name

                              AND c1.COLUMN_NAME = c.name

                               and c1.TABLE_SCHEMA = sc.name 

left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c2 on c2.TABLE_NAME = o.name                                                                                                                      and c2.CONSTRAINT_SCHEMA = c1.TABLE_SCHEMA                             

WHERE o.name = @TableName

order by c1.TABLE_SCHEMA, o.name, c1.ORDINAL_POSITION