Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Table Schema Documentation


 / Published in: SQL
 

  1. /*
  2.   SQL Server Display Table schema
  3.  
  4.   This script shows table schema - very useful for documentation purposes
  5. */
  6.  
  7. DECLARE @TABLENAME nvarchar(100)
  8.  
  9. SET @TABLENAME = '[Enter the table name here]'
  10. -- e.g. SET @TABLENAME = 'sizerange'
  11.  
  12. SELECT sc.[name] AS 'Column Name',
  13. CASE
  14. WHEN sep.[value] IS NULL THEN ''
  15. ELSE sep.[value]
  16. END AS 'Description',
  17. sst.[name] AS 'Data Type',
  18. CASE sst.[length]
  19. WHEN 8000 THEN 'MAX'
  20. ELSE CAST(sst.[length] AS nvarchar)
  21. END AS 'Length',
  22. CASE
  23. WHEN sdc.[definition] IS NULL THEN ''
  24. ELSE sdc.[definition]
  25. END AS 'Default',
  26. CASE
  27. WHEN sic.[is_identity] IS NULL THEN ''
  28. ELSE '(' + CAST(sic.[seed_value] AS nvarchar) + ',' + CAST(sic.[increment_value] AS nvarchar) + ')'
  29. END AS 'ID',
  30. CASE
  31. WHEN si.[is_descending_key] = 0 THEN 'ASC'
  32. WHEN si.[is_descending_key] = 1 THEN 'DESC'
  33. ELSE ''
  34. END AS 'PK',
  35. CASE
  36. WHEN sfk.[referenced_object_id] IS NULL THEN ''
  37. ELSE (SELECT [name] FROM [sys].[TABLES] WHERE [object_id] = sfk.[referenced_object_id]) + '.' + (SELECT [name] FROM [sys].[COLUMNS] WHERE [object_id] = sfk.[referenced_object_id] AND [column_id] = sfk.[referenced_column_id])
  38. END AS 'FK',
  39. CASE
  40. WHEN sc.[is_nullable] = 0 THEN 'N'
  41. WHEN sc.[is_nullable] = 1 THEN 'Y'
  42. ELSE ''
  43. END AS 'NULL'
  44. FROM [sys].[COLUMNS] sc
  45. INNER JOIN [sys].[TABLES] st
  46. ON sc.[object_id] = st.[object_id]
  47. INNER JOIN [sys].[systypes] sst
  48. ON sc.[system_type_id] = sst.[xtype]
  49. LEFT OUTER JOIN [sys].[identity_columns] sic
  50. ON sc.[object_id] = sic.[object_id] AND sc.[column_id] = sic.[column_id]
  51. LEFT OUTER JOIN [sys].[extended_properties] sep
  52. ON sc.[object_id] = sep.[major_id] AND sc.[column_id] = sep.[minor_id] AND sep.[name] = 'MS_Description'
  53. LEFT OUTER JOIN [sys].[default_constraints] sdc
  54. ON sc.[object_id] = sdc.[parent_object_id] AND sc.[column_id] = sdc.[parent_column_id] AND sdc.[type] = 'D'
  55. LEFT OUTER JOIN [sys].[index_columns] si
  56. ON sc.[object_id] = si.[object_id] AND sc.[column_id] = si.[column_id]
  57. LEFT OUTER JOIN [sys].[foreign_key_columns] sfk
  58. ON sc.[object_id] = sfk.[parent_object_id] AND sc.[column_id] = sfk.[parent_column_id]
  59. WHERE st.[name] = @TABLENAME AND sst.[name] <> 'sysname'
  60. ORDER BY sc.[column_id]

Report this snippet  

You need to login to post a comment.