T-SQL List all columns in all tables

January 13th, 2012
by Kristof

If you've ever wanted to get a listing of all the columns in all of the tables in a certain database, here's how. I use this often for writing technical documentation on systems.

This little code will list all of your tables, columns, datatypes, nullable option, identity option and will also indicate primary and foreign keys and default constraints.

  1.  
  2. SELECT
  3. -- distinct
  4. -- quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name) as TableName,
  5. -- col.column_id,
  6. col.name AS ColumnName,
  7. type_name(col.user_type_id)+
  8. CASE
  9. WHEN COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'scale') IS null THEN
  10. ' (' + CONVERT(VARCHAR,COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'precision')) + ')' --is null --convert(varchar, col.max_length)
  11. WHEN col.user_type_id in (60, 106, 108, 122) THEN
  12. ' (' + CONVERT(VARCHAR,col.PRECISION) + ',' + CONVERT(VARCHAR, col.scale) + ')'
  13. ELSE ''
  14. END AS DataType,
  15. CASE col.is_nullable
  16. WHEN 0 THEN 'NOT '
  17. ELSE ''
  18. END
  19. + 'NULL' AS Nullable,
  20. CASE col.is_identity
  21. WHEN 0 THEN ''
  22. ELSE 'IDENTITY (' + CONVERT(VARCHAR, idc.seed_value) + ',' + CONVERT(VARCHAR, idc.increment_value) + ')'
  23. END AS [IDENTITY],
  24. CASE
  25. WHEN ixc.column_id IS not null THEN
  26. 'PK'
  27. WHEN fkc.constraint_object_id IS not null THEN
  28. QUOTENAME(schema_name(tb2.schema_id)) + N'.' + QUOTENAME(tb2.name) + N'.' + fcl.name
  29. ELSE ''
  30. END AS Key_Constraint,
  31. CASE
  32. WHEN dfc.OBJECT_ID IS not null THEN
  33. dfc.definition
  34. ELSE ''
  35. END AS [DEFAULT CONSTRAINT]
  36. FROM
  37. sys.tables tbl
  38. INNER join
  39. (
  40. SELECT
  41. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  42. col.name,
  43. col.column_id,
  44. col.OBJECT_ID,
  45. col.is_identity,
  46. col.is_nullable,
  47. col.PRECISION,
  48. col.scale,
  49. col.user_type_id,
  50. 1 AS SortKey
  51. FROM
  52. sys.columns col
  53. INNER join
  54. sys.tables tbl
  55. ON tbl.OBJECT_ID = col.OBJECT_ID
  56.  
  57. UNION
  58.  
  59. SELECT
  60. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  61. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
  62. 0,
  63. tbl.OBJECT_ID,
  64. 0,
  65. 0,
  66. 0,
  67. 0,
  68. 0,
  69. 0
  70. FROM
  71. sys.tables tbl
  72.  
  73.  
  74. ) col
  75. ON tbl.OBJECT_ID = col.OBJECT_ID
  76. LEFT join
  77. sys.key_constraints pkc
  78. ON pkc.parent_object_id = tbl.OBJECT_ID
  79. and pkc.type = 'PK'
  80. LEFT join
  81. sys.index_columns ixc
  82. ON ixc.OBJECT_ID = tbl.OBJECT_ID
  83. and ixc.index_id = pkc.unique_index_id
  84. and ixc.column_id = col.column_id
  85. LEFT join
  86. sys.foreign_key_columns fkc
  87. ON fkc.parent_object_id = tbl.OBJECT_ID
  88. and fkc.parent_column_id = col.column_id
  89. LEFT join
  90. sys.columns fcl
  91. ON fkc.referenced_column_id = fcl.column_id
  92. and fkc.referenced_object_id = fcl.OBJECT_ID
  93. LEFT join
  94. sys.tables tb2
  95. ON fcl.OBJECT_ID = tb2.OBJECT_ID
  96. LEFT join
  97. sys.default_constraints dfc
  98. ON dfc.parent_object_id = tbl.OBJECT_ID
  99. and dfc.parent_column_id = col.column_id
  100. LEFT join
  101. sys.identity_columns idc
  102. ON idc.OBJECT_ID = tbl.OBJECT_ID
  103. and idc.column_id = col.column_id
  104. WHERE
  105. tbl.type = 'U'
  106. ORDER BY
  107. QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name),
  108. SortKey,
  109. ColumnName
  110.  

Posted in Hacks, Sql | Comments (0)

No comments yet

Leave a Reply

SEO Powered by Platinum SEO from Techblissonline