Table Schema Documentation

/* 
  SQL Server Display Table schema
 
  This script shows table schema - very useful for documentation purposes
*/
 
DECLARE @TABLENAME nvarchar(100)
 
SET @TABLENAME = '[Enter the table name here]'
-- e.g. SET @TABLENAME = 'sizerange'
 
SELECT			sc.[name] AS 'Column Name', 
				CASE 
					WHEN sep.[value] IS NULL THEN ''
					ELSE sep.[value]
				END AS 'Description', 
				sst.[name] AS 'Data Type', 
				CASE sst.[length]
					WHEN 8000 THEN 'MAX'
					ELSE CAST(sst.[length] AS nvarchar) 
				END AS 'Length', 
				CASE 
					WHEN sdc.[definition] IS NULL THEN '' 
					ELSE sdc.[definition]
				END AS 'Default', 
				CASE 
					WHEN sic.[is_identity] IS NULL THEN ''
					ELSE '(' + CAST(sic.[seed_value] AS nvarchar) + ',' + CAST(sic.[increment_value] AS nvarchar) + ')' 
				END AS 'ID', 
				CASE  
					WHEN si.[is_descending_key] = 0 THEN 'ASC' 
					WHEN si.[is_descending_key] = 1 THEN 'DESC' 
					ELSE '' 
				END AS 'PK', 
				CASE 
					WHEN sfk.[referenced_object_id] IS NULL THEN ''
					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]) 
				END AS 'FK',
				CASE 
					WHEN sc.[is_nullable] = 0 THEN 'N'
					WHEN sc.[is_nullable] = 1 THEN 'Y'
					ELSE ''
				END	AS 'NULL'
FROM			[sys].[COLUMNS] sc 
INNER JOIN		[sys].[TABLES] st 
ON				sc.[object_id] = st.[object_id]
INNER JOIN		[sys].[systypes] sst
ON				sc.[system_type_id] = sst.[xtype] 
LEFT OUTER JOIN	[sys].[identity_columns] sic 
ON				sc.[object_id] = sic.[object_id] AND sc.[column_id] = sic.[column_id] 
LEFT OUTER JOIN [sys].[extended_properties] sep 
ON				sc.[object_id] = sep.[major_id] AND sc.[column_id] = sep.[minor_id] AND sep.[name] = 'MS_Description'
LEFT OUTER JOIN [sys].[default_constraints] sdc 
ON				sc.[object_id] = sdc.[parent_object_id] AND sc.[column_id] = sdc.[parent_column_id] AND sdc.[type] = 'D'
LEFT OUTER JOIN [sys].[index_columns] si
ON				sc.[object_id] = si.[object_id] AND sc.[column_id] = si.[column_id]
LEFT OUTER JOIN [sys].[foreign_key_columns] sfk 
ON				sc.[object_id] = sfk.[parent_object_id] AND sc.[column_id] = sfk.[parent_column_id]
WHERE			st.[name] = @TABLENAME AND sst.[name] <> 'sysname'
ORDER BY		sc.[column_id]