DECLARE @schemaName sysname = N'dbo',
@tableName sysname = N'Persons';
-- Columns in a given table
SELECT ORDINAL_POSITION AS [N],
COLUMN_NAME AS [ColumnName],
DATA_TYPE
+ ISNULL('(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(5)) + ')', '')
+ ISNULL(
CASE
WHEN DATA_TYPE IN ('decimal', 'numeric', 'float', 'real')
THEN '(' + CAST(NUMERIC_PRECISION AS varchar(5)) + ', ' + CAST(NUMERIC_SCALE AS varchar(5)) + ')'
ELSE ''
END
, '')
AS [ColumnType],
CASE IS_NULLABLE WHEN 'YES' THEN 'null' ELSE '' END AS [AllowNull]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schemaName
AND TABLE_NAME = @tableName;
-- Keys
WITH ForeignKey_CTE
AS
(
-- source: https://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx
SELECT
KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
, KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
, KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
, KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
WHERE KCU1.TABLE_SCHEMA = @schemaName
AND KCU1.TABLE_NAME = @tableName
)
SELECT k.COLUMN_NAME AS [ColumnName],
k.CONSTRAINT_NAME AS [KeyName],
c.UQ_TABLE_NAME AS [ReferencingTable],
c.UQ_COLUMN_NAME AS [ReferencingColumn]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
LEFT JOIN ForeignKey_CTE c ON k.CONSTRAINT_NAME = c.FK_CONSTRAINT_NAME
WHERE k.TABLE_SCHEMA = @schemaName
AND k.TABLE_NAME = @tableName;
-- Indexes
SELECT
i.name AS IndexName,
i.type_desc,
n.ColumnList
FROM sys.indexes i
CROSS APPLY (
SELECT STUFF(
( SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE i.object_id = ic.object_id and i.index_id = ic.index_id
ORDER BY ic.index_column_id
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),
1, 2, '')
) n (ColumnList)
WHERE i.object_id = OBJECT_ID(@tableName, 'Table')
ORDER BY i.index_id;