Listing Columns, Keys and Indexes For Specific Table – Andrey Zavadskiy

Now I’m investigating Entity Framework and how it creates tables and other database objects. It’s surprising that if you have a reference (foreign key) in your model, EF creates an index in the referenced table during the migration process by default. So, if I have just 5 distinct referencing values with a roughly equal quantity of referenced records (in this case about 20% for each value), SQL Server will never use that index.
To put the creation of database objects under control, I need a simple script to see what objects are really created after migration. There are 3 queries in the script that list the following information:

columns, their data types and null/not null information
primary/foreign keys, and referencing table and columns
clustered/non-clustered indexes and columns

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;

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

(

-- 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;

Leave a Reply Cancel reply