Cacher is the code snippet organizer for pro developers

DECLARE @TableName VARCHAR(MAX)
SET @TableName =''
 
SELECT A.[資料表名稱], A.[欄位名稱], A.[型態/長度], A.[Null], A.[索引], A.[預設值], A.[說明]
 FROM (
 	SELECT sysobjects.id, NULL AS colid
	, sysobjects.name AS [資料表名稱]
	, SPACE(0) AS [欄位名稱], SPACE(0) AS [型態/長度], SPACE(0) [Null], SPACE(0) AS [索引], SPACE(0) AS [預設值]
	, CASE WHEN ISNULL(properties.value, '') = '' THEN '[尚未增加說明]' ELSE properties.value END AS [說明]
	FROM dbo.sysobjects 
	LEFT JOIN sys.extended_properties properties ON sysobjects.id = properties.major_id AND properties.minor_id = 0
	WHERE sysobjects.name = @TableName
	UNION
	SELECT sysobjects.id, syscolumns.colid
	, sysobjects.name AS [資料表名稱]
	-- , sysproperties.[value] AS 表說明
	, syscolumns.name AS [欄位名稱]
	-- , properties.[value] AS 欄位說明
	, CASE WHEN systypes.name IN ('char', 'nchar', 'varchar') THEN systypes.name + '(' + CASE WHEN syscolumns.length = -1 THEN 'MAX' ELSE CAST(syscolumns.length AS VARCHAR) END + ')'
	WHEN systypes.name IN ('nvarchar') THEN systypes.name + '(' + CASE WHEN syscolumns.length = -1 THEN 'MAX' ELSE CAST((syscolumns.length / 2) AS VARCHAR) END + ')'
	WHEN systypes.name IN ('decimal','numeric') THEN systypes.name + '(' + CAST(syscolumns.prec AS VARCHAR) + ',' + CAST(syscolumns.scale AS VARCHAR) + ')' 
	ELSE systypes.name END AS [型態/長度]
	, CASE WHEN syscolumns.isnullable=0 THEN '' ELSE 'V' END AS [Null]
	, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid = syscolumns.colid))) THEN 'PK' ELSE CASE WHEN sysforeignkeys.constid IS NULL  THEN '' ELSE 'FK' END END AS [索引]
	, CASE WHEN syscomments.text IS NULL THEN '' ELSE LEFT(RIGHT(syscomments.text, LEN(syscomments.text) - 1), LEN(syscomments.text) - 2) END AS [預設值]
	, CASE WHEN ISNULL(properties.value, '') = '' THEN '[尚未增加說明]' ELSE properties.value END AS [說明]
	FROM syscolumns																															-- 資料表字段 
	INNER JOIN sysobjects ON sysobjects.id = syscolumns.id																					-- 資料物件 
	INNER JOIN systypes ON syscolumns.xtype = systypes.xtype																				-- 資料型別 
	LEFT OUTER JOIN sys.extended_properties properties ON syscolumns.id = properties.major_id AND syscolumns.colid = properties.minor_id	-- 欄位屬性資訊 
	LEFT OUTER JOIN sys.extended_properties sysproperties ON sysobjects.id = sysproperties.major_id AND sysproperties.minor_id = 0			-- 表屬性資訊 
	LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id																		-- 註釋資訊 
	LEFT OUTER JOIN sysindexkeys ON sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid								-- 索引中的鍵或列的資訊 
	LEFT OUTER JOIN sysindexes ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid									-- 資料庫 索引表 
	LEFT OUTER JOIN sysforeignkeys ON sysforeignkeys.fkeyid = syscolumns.id AND sysforeignkeys.fkey = syscolumns.colid
	WHERE (sysobjects.xtype ='U') 
	AND systypes.name NOT IN ('sysname')
	AND sysobjects.name = @TableName
	UNION 
	SELECT 2147483647 - 1 as id, 32767 - 1 as colid, '', '', '', '', '', '', ''
	UNION
	SELECT 2147483647 as id, 32767 as colid, '', '', '', '', '', '', '最後檢查時間：' + convert(varchar, getdate(), 120)
) A
order by A.id, A.colid

DECLARE @TableName VARCHAR(MAX)
SET @TableName =''
 
SELECT A.[資料表名稱], A.[欄位名稱], A.[型態/長度], A.[Null], A.[索引], A.[預設值], A.[說明]
 FROM (
	SELECT sysobjects.id, syscolumns.colid
	, sysobjects.name AS [資料表名稱]
	-- , sysproperties.[value] AS 表說明
	, syscolumns.name AS [欄位名稱]
	-- , properties.[value] AS 欄位說明
	, CASE WHEN systypes.name IN ('char', 'nchar', 'varchar') THEN systypes.name + '(' + CASE WHEN syscolumns.length = -1 THEN 'MAX' ELSE CAST(syscolumns.length AS VARCHAR) END + ')'
	WHEN systypes.name IN ('nvarchar') THEN systypes.name + '(' + CASE WHEN syscolumns.length = -1 THEN 'MAX' ELSE CAST((syscolumns.length / 2) AS VARCHAR) END + ')'
	WHEN systypes.name IN ('decimal','numeric') THEN systypes.name + '(' + CAST(syscolumns.prec AS VARCHAR) + ',' + CAST(syscolumns.scale AS VARCHAR) + ')' 
	ELSE systypes.name END AS [型態/長度]
	, CASE WHEN syscolumns.isnullable=0 THEN '' ELSE 'V' END AS [Null]
	, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid = syscolumns.colid))) THEN 'PK' ELSE CASE WHEN sysforeignkeys.constid IS NULL  THEN '' ELSE 'FK' END END AS [索引]
	, CASE WHEN syscomments.text IS NULL THEN '' ELSE LEFT(RIGHT(syscomments.text, LEN(syscomments.text) - 1), LEN(syscomments.text) - 2) END AS [預設值]
	, CASE WHEN ISNULL(properties.value, '') = '' THEN '[尚未增加說明]' ELSE properties.value END AS [說明]
	FROM syscolumns																															-- 資料表字段 
	INNER JOIN sysobjects ON sysobjects.id = syscolumns.id																					-- 資料物件 
	INNER JOIN systypes ON syscolumns.xtype = systypes.xtype																				-- 資料型別 
	LEFT OUTER JOIN sys.extended_properties properties ON syscolumns.id = properties.major_id AND syscolumns.colid = properties.minor_id	-- 欄位屬性資訊 
	LEFT OUTER JOIN sys.extended_properties sysproperties ON sysobjects.id = sysproperties.major_id AND sysproperties.minor_id = 0			-- 表屬性資訊 
	LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id																		-- 註釋資訊 
	LEFT OUTER JOIN sysindexkeys ON sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid								-- 索引中的鍵或列的資訊 
	LEFT OUTER JOIN sysindexes ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid									-- 資料庫 索引表 
	LEFT OUTER JOIN sysforeignkeys ON sysforeignkeys.fkeyid = syscolumns.id AND sysforeignkeys.fkey = syscolumns.colid
	WHERE (sysobjects.xtype ='U') 
	AND systypes.name NOT IN ('sysname')
	AND sysobjects.name = @TableName
	UNION 
	SELECT 2147483647 - 1 as id, 32767 - 1 as colid, '', '', '', '', '', '', ''
	UNION
	SELECT 2147483647 as id, 32767 as colid, '', '', '', '', '', '', '最後檢查時間：' + convert(varchar, getdate(), 120)
) A
order by A.id, A.colid

DECLARE @TableName VARCHAR(MAX)
SET @TableName =''

SELECT sysobjects.name AS [資料表名稱]
-- , sysproperties.[value] AS 表說明
, syscolumns.name AS [欄位名稱]
-- , properties.[value] AS 欄位說明
, CASE WHEN systypes.name IN ('char', 'nchar', 'varchar', 'nvarchar') THEN systypes.name + '(' + CASE WHEN syscolumns.length = -1 THEN 'MAX' ELSE CAST(syscolumns.length AS VARCHAR) END + ')'
WHEN systypes.name IN ('decimal','numeric') THEN systypes.name + '(' + CAST(syscolumns.prec AS VARCHAR) + ',' + CAST(syscolumns.scale AS VARCHAR) + ')' 
ELSE systypes.name END AS [型態/長度]
, CASE WHEN syscolumns.isnullable=0 THEN '' ELSE 'V' END AS [Null]
, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid = syscolumns.colid))) THEN 'PK' ELSE CASE WHEN sysforeignkeys.constid IS NULL  THEN '' ELSE 'FK' END END AS [索引]
, CASE WHEN syscomments.text IS NULL THEN '' ELSE LEFT(RIGHT(syscomments.text, LEN(syscomments.text) - 1), LEN(syscomments.text) - 2) END AS [預設值]
, CASE WHEN ISNULL(properties.value, '') = '' THEN '[尚未增加說明]' ELSE properties.value END AS [說明]
FROM syscolumns																															-- 資料表字段 
INNER JOIN sysobjects ON sysobjects.id = syscolumns.id																					-- 資料物件 
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype																				-- 資料型別 
LEFT OUTER JOIN sys.extended_properties properties ON syscolumns.id = properties.major_id AND syscolumns.colid = properties.minor_id	-- 欄位屬性資訊 
LEFT OUTER JOIN sys.extended_properties sysproperties ON sysobjects.id = sysproperties.major_id AND sysproperties.minor_id = 0			-- 表屬性資訊 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id																		-- 註釋資訊 
LEFT OUTER JOIN sysindexkeys ON sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid								-- 索引中的鍵或列的資訊 
LEFT OUTER JOIN sysindexes ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid									-- 資料庫 索引表 
LEFT OUTER JOIN sysforeignkeys ON sysforeignkeys.fkeyid = syscolumns.id AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype ='U') 
AND systypes.name NOT IN ('sysname')
AND sysobjects.name = @TableName
order by sysobjects.id, syscolumns.colid

DECLARE @TableName VARCHAR(MAX)
SET @TableName =''

SELECT sysobjects.name AS [資料表名稱]
-- , sysproperties.[value] AS 表說明
, syscolumns.name AS [欄位名稱]
-- , properties.[value] AS 欄位說明
, systypes.name AS [欄位型別]
, syscolumns.length AS [欄位長度]
, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,'Scale'), 0) AS [小數位數]
, CASE WHEN syscolumns.isnullable=0 THEN '' ELSE 'V' END AS [是否為空]
, CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [預設值]
, CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name,'IsIdentity')= 1 THEN 'V' ELSE '' END AS [遞增欄位]
, CASE WHEN sysindexes.name IS NULL THEN''ELSE sysindexes.name END AS [索引名稱]
, CASE WHEN sysindexkeys.keyno IS NULL THEN''ELSE CONVERT(VARCHAR(10),sysindexkeys.keyno ) END AS [索引位置]
, CASE WHEN sysindexes.indid=1 THEN '叢集索引' WHEN sysindexes.indid>1 AND sysindexes.indid<>255 THEN '非叢集索引' WHEN sysindexes.indid IS NULL THEN '' ELSE '其他' END AS [索引型別]
, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid = syscolumns.colid))) THEN 'V' ELSE '' END AS [主鍵]
, CASE WHEN sysforeignkeys.constid IS NULL  THEN '' ELSE 'V' END AS [外鍵]
, properties.value AS [說明]
FROM syscolumns																															-- 資料表字段 
INNER JOIN sysobjects ON sysobjects.id = syscolumns.id																					-- 資料物件 
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype																				-- 資料型別 
LEFT OUTER JOIN sys.extended_properties properties ON syscolumns.id = properties.major_id AND syscolumns.colid = properties.minor_id	-- 欄位屬性資訊 
LEFT OUTER JOIN sys.extended_properties sysproperties ON sysobjects.id = sysproperties.major_id AND sysproperties.minor_id = 0			-- 表屬性資訊 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id																		-- 註釋資訊 
LEFT OUTER JOIN sysindexkeys ON sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid								-- 索引中的鍵或列的資訊 
LEFT OUTER JOIN sysindexes ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid									-- 資料庫 索引表 
LEFT OUTER JOIN sysforeignkeys ON sysforeignkeys.fkeyid = syscolumns.id AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype ='U') 
AND sysobjects.name = @TableName
order by sysobjects.id, syscolumns.colid