SQL statement to get the most complete data dictionary for SQL Server

Get the most complete data dictionary for SQL Server

The following code is executed in Microsoft SQL Server 2013 by

SELECTSysobjects.name astable name,--sys.extended_properties. [Value] As table description,Syscolumns.name asfield name,--Properties. [Value] As field description,Systypes.name asfield type, Syscolumns.length asfield Length,ISNULL(ColumnProperty(Syscolumns.id, Syscolumns.name,' Scale'),0) asNumber of decimal digits, Case  whenSyscolumns.isnullable=0  Then "'ELSE '√'END  asis empty, Case  whensyscomments.text  is NULL  Then "' ELSEsyscomments.textEND  asdefault value, Case  when ColumnProperty(Syscolumns.id, Syscolumns.name,'isidentity')= 1  Then '√' ELSE "'END  asincrement field, Case  whenSysindexes.name is NULL  Then "'ELSESysindexes.nameEND  asindex name, Case  whenSysindexkeys.keyno is NULL  Then "'ELSE CONVERT(VARCHAR(Ten), Sysindexkeys.keyno)END  asindex location, Case  whenSysindexes.indid=1  Then 'Clustered Index' whenSysindexes.indid>1  andSysindexes.indid<>255  Then 'Nonclustered Indexes' whenSysindexes.indid is NULL  Then "'ELSE 'other'END  asindex Type, Case  when EXISTS(SELECT 1   fromsysobjectsWHEREXtype= 'PK'  andNameinch (SELECTname fromsysindexesWHEREIndidinch   (SELECTindid fromSysindexkeysWHEREId=Syscolumns.id andColid=syscolumns.colid ))) Then '√' ELSE "'END  asprimary KEY, Case  whenSysforeignkeys.constid is NULL  Then "'ELSE '√'END  asExternal Health fromsyscolumns--Data table fieldINNER JOINsysobjects--Data Objects   onSysobjects.id=syscolumns.idINNER JOINSystypes--Data Type   onSyscolumns.xtype=Systypes.xtype Left OUTER JOINSys.extended_properties Properties--Field Property Information   onSyscolumns.id=Properties. minor_id andSyscolumns.colid=Properties. minor_id Left OUTER JOINSys.extended_properties--Table Property Information   onSysobjects.id=sys.extended_properties. minor_id andSys.extended_properties. minor_id= 0 Left OUTER JOINsyscomments--Comment Information   onSyscolumns.cdefault=syscomments.id Left OUTER JOINSysindexkeys--information for keys or columns in an index   onSysindexkeys.id=syscolumns.id andSysindexkeys.colid=Syscolumns.colid Left OUTER JOINsysindexes--Database Index Table   onSysindexes.id=sysindexkeys.id andSysindexes.indid=Sysindexkeys.indid Left OUTER JOINSysforeignkeys onSysforeignkeys.fkeyid=syscolumns.id andSysforeignkeys.fkey=Syscolumns.colidWHERE(Sysobjects.xtype= 'U')Order  bySysobjects.id,syscolumns.colid

Reproduced in http://blog.csdn.net/baoqiangwang/article/details/4695361

SQL statement to get the most complete data dictionary for SQL Server