SQL: Revised list of table columns, with data types, keys, constraints, etc.

I have just improved the script 1, in SQL: List of table columns, with data types:

It now includes an indicate for nullable fields, primary and foreign keys, unique and check constraints, and identity columns

SELECT
      schema_name(O.schema_id) + '.' + O.name AS TableName,
      C.name AS ColumnName,
      T.name AS Type,
      C.max_length AS Length,
	  CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE NULL END AS Nullable,
	  CASE WHEN P.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS PrimaryKey,
	  CASE WHEN F.ReferencingColumN IS NOT NULL THEN 'YES' ELSE NULL END AS ForeignKey,
	  CASE WHEN U.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS [Unique],
	  CC.Definition AS CheckConstraint,
	  CASE WHEN c.is_identity = 1 THEN 'YES' ELSE NULL END AS [Identity]
FROM        sys.objects O
INNER JOIN  sys.columns C ON O.object_id = C.object_id
INNER JOIN  sys.types T ON C.user_type_id = T.user_type_id
LEFT JOIN (
	SELECT p.name AS ParentTable, pc.name AS ParentColumn, r.name AS ReferencingTable, rc.name AS ReferencingColumn
	FROM sys.foreign_key_columns f
	JOIN sys.objects p ON f.parent_object_id = p.object_id
	JOIN sys.objects r ON f.referenced_object_id = r.object_id
	JOIN sys.columns pc ON f.parent_object_id = pc.object_id AND f.parent_column_id = pc.column_id
	JOIN sys.columns rc ON f.referenced_object_id = rc.object_id AND f.referenced_column_id = rc.column_id
) AS F ON O.name = F.ParentTable AND C.name = F.ParentColumn
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_primary_key = 1
) AS P ON O.name = P.TableName AND C.name = P.ColumnName
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_unique_constraint = 1
) AS U ON O.name = U.TableName AND C.name = U.ColumnName
LEFT JOIN(
	SELECT co.name AS TableName, c.name AS ColumnName, definition 
	FROM sys.check_constraints cc
	JOIN sys.objects co ON cc.parent_object_id = co.object_id
	JOIN sys.columns c ON co.object_id = c.object_id AND cc.parent_column_id = c.column_id
) AS CC ON O.name = CC.TableName AND C.name = CC.ColumnName
WHERE O.type = 'U'
ORDER BY
      schema_name(O.schema_id),
      O.name,
      C.column_id
GO
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.
Updated: Monday, 27th February, 2017 — 2:12 pm

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.