14.2.6 查询表结构字典.sql

sqlserver 数据库编程的绝好脚本

SELECT TableName=o.name,OWNER=USER_NAME(o.uid),TableDescription=ISNULL(ptb.value,N''),
	FieldId=c.colid,FieldName=c.name,
	FieldType=QUOTENAME(t.name)
		+CASE 
			WHEN t.name IN (N'decimal',N'numeric')
			THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
			WHEN t.name=N'float'
				OR t.name like N'%char'
				OR t.name like N'%binary'
			THEN N'('+CAST(c.prec as varchar)+N')'
			ELSE N'' END
		+CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END+N' NULL',
	FieldDescription=ISNULL(pfd.value,''),
	DefileLength=c.length,
	FieldDefault=ISNULL(df.text,N''),
	IsIDENTITY=COLUMNPROPERTY(o.id,c.name,N'IsIdentity'),
	IsComputed=COLUMNPROPERTY(o.id,c.name,N'IsComputed'),
	IsROWGUID=COLUMNPROPERTY(o.id,c.name,N'IsRowGuidCol'),
	IsPrimaryKey=CASE WHEN opk.xtype IS NULL THEN 0 ELSE 1 END
FROM sysobjects o
	JOIN syscolumns c
		ON c.id=o.id
			AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
	JOIN systypes t
		ON t.xusertype=c.xusertype
	LEFT JOIN syscomments df
		ON df.id=c.cdefault
	LEFT JOIN sysproperties ptb
		ON ptb.id=o.id and ptb.smallid=0
	LEFT JOIN sysproperties pfd
		ON pfd.id=o.id and pfd.smallid=c.colid
	LEFT JOIN sysindexkeys idxk
		ON idxk.id=o.id
			AND idxk.colid=c.colid
	LEFT JOIN sysindexes idx
		ON idx.indid=idxk.indid
			AND idx.id=idxk.id
			AND idx.indid NOT IN(0,255)
	LEFT JOIN sysobjects opk
		ON opk.parent_obj=o.id
			AND opk.name=idx.name
			AND OBJECTPROPERTY(opk.id,N'IsPrimaryKey')=1
ORDER BY o.name,c.colid