columnproperty server sql_sqlserver获取所有表和表字段

SELECT

[number]=a.colorder,

[column] =a.name,

[datatype]=b.name,

[length]=COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),

[identity]=case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end,

[key]=case when exists(SELECT 1 FROM sysobjects where xtype=‘PK‘ and parent_obj=a.id and name in (

SELECT name FROM sysindexes WHERE indid in(

SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid

))) then ‘√‘ else ‘‘ end,

[isnullable]=case when a.isnullable=1 then ‘√‘else ‘‘ end,

[default]=isnull(e.text,‘‘),

[remark]=isnull(g.[value],‘‘)

FROM syscolumns a

left join systypes b on a.xusertype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘

left join syscomments e on a.cdefault=e.id

left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id

left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0

where d.name=‘BPMS_Organization‘ order by a.id,a.colorder

SELECT ID = D.ID ,

Field = CASE WHEN A.COLORDER = 1 THEN D.NAME

ELSE ‘‘

END ,

Remark = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, ‘‘)

ELSE ‘‘

END ,

ParentID = 0 ,

colorder = 0

FROM SYSCOLUMNS A

LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE

INNER JOIN SYSOBJECTS D ON A.ID = D.ID

AND D.XTYPE = ‘U‘

AND D.NAME <> ‘DTPROPERTIES‘

LEFT JOIN sys.extended_properties F ON D.ID = F.major_id

WHERE a.COLORDER = 1

AND F.minor_id = 0

DECLARE @TableInfo TABLE

(

name VARCHAR(50) ,

[rows] CHAR(11) ,

reserved VARCHAR(50) ,

data VARCHAR(50) ,

index_size VARCHAR(50) ,

unused VARCHAR(50)

)

DECLARE @TableName TABLE ( name VARCHAR(50) )

DECLARE @name VARCHAR(50)

INSERT INTO @TableName

( name

)

SELECT o.name

FROM sysobjects o ,

sysindexes i

WHERE o.id = i.id

AND o.Xtype = ‘U‘

AND i.indid < 2

ORDER BY i.rows DESC ,

o.name

WHILE EXISTS ( SELECT 1

FROM @TableName )

BEGIN

SELECT TOP 1

@name = name

FROM @TableName

DELETE @TableName WHERE name=@name

INSERT INTO @TableInfo

( name ,

[rows] ,

reserved ,

data ,

index_size ,

unused

)

EXEC sys.sp_spaceused @name

END

--表名,记录数,使用大小,索引所使使用大小,未用的空间量,表说明

SELECT F.*,p.tdescription FROM @TableInfo F LEFT JOIN (

SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME

ELSE ‘‘

END ,

tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, ‘‘)

ELSE ‘‘

END

FROM SYSCOLUMNS A

LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE

INNER JOIN SYSOBJECTS D ON A.ID = D.ID

AND D.XTYPE = ‘U‘

AND D.NAME <> ‘DTPROPERTIES‘

LEFT JOIN sys.extended_properties F ON D.ID = F.major_id

WHERE a.COLORDER = 1

AND F.minor_id = 0

)P ON F.name=p.name

SELECT a.name

FROM SYSCOLUMNS A

INNER JOIN SYSOBJECTS D ON A.ID = D.ID

AND D.XTYPE = ‘U‘

AND D.NAME <> ‘DTPROPERTIES‘

WHERE d.name = ‘BPMS_Button‘ AND EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE XTYPE = ‘PK‘ AND PARENT_OBJ = A.ID AND NAME IN ( SELECT NAME FROM SYSINDEXES WHERE INDID IN ( SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID = A.COLID ) ) )