[SQL Server] SQL Server 2005/2008 获取数据表全部信息
1 SELECT * FROM (
2 SELECT TableName=D.NAME,TableExplain=CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,' ') ELSE ' ' END,
3 ColumnName=A.NAME,IsIdentity = COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY '),
4 IsPrimaryKey=CASE WHEN EXISTS(Select 1 FROM SYSOBJECTS Where XTYPE='PK ' AND PARENT_OBJ=A.ID AND NAME IN (
5 SELECT NAME FROM SYSINDEXES Where INDID IN(
6 SELECT INDID FROM SYSINDEXKEYS Where ID = A.ID AND COLID=A.COLID))) THEN 1 ELSE 0 END,
7 ColumnType=B.NAME,A.LENGTH,A.ISNULLABLE,DefaultValue=ISNULL(E.TEXT,' '),ColumnExplain=ISNULL(G.[VALUE],' '),
8 Scale= ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE '),0)
9 FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
10 INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U ' AND D.NAME<>'DTPROPERTIES '
11 LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID LEFT JOIN sys.extended_properties G
12 ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F
13 ON D.ID=F.major_id AND F.minor_id=0
14 ) X
15 WHERE
16 X.TableName = '表名称'
2 SELECT TableName=D.NAME,TableExplain=CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,' ') ELSE ' ' END,
3 ColumnName=A.NAME,IsIdentity = COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY '),
4 IsPrimaryKey=CASE WHEN EXISTS(Select 1 FROM SYSOBJECTS Where XTYPE='PK ' AND PARENT_OBJ=A.ID AND NAME IN (
5 SELECT NAME FROM SYSINDEXES Where INDID IN(
6 SELECT INDID FROM SYSINDEXKEYS Where ID = A.ID AND COLID=A.COLID))) THEN 1 ELSE 0 END,
7 ColumnType=B.NAME,A.LENGTH,A.ISNULLABLE,DefaultValue=ISNULL(E.TEXT,' '),ColumnExplain=ISNULL(G.[VALUE],' '),
8 Scale= ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE '),0)
9 FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
10 INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U ' AND D.NAME<>'DTPROPERTIES '
11 LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID LEFT JOIN sys.extended_properties G
12 ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F
13 ON D.ID=F.major_id AND F.minor_id=0
14 ) X
15 WHERE
16 X.TableName = '表名称'
