List Table and Column information in a database
Just a quick script to list all schema’s, tables and column information from within a database.
SELECT
(S.[name] + ‘.’ + T.[name]) AS [Table],
C.[name] AS [Column Name],
D.[name] AS [Date Type],
C.max_length AS [Length],
C.precision AS [Precision],
C.scale AS [Scale],
C.is_nullable AS [Nullable],
C.is_identity AS [Identity Column],
C.[collation_name] AS [Collation]
FROM sys.tables T INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
INNER JOIN sys.columns C
ON T.object_id = C.object_id
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.[name], C.column_id