SQLServer数据库系统表应用

查询数据库表和所有字段信息

SELECT d.name 'TABLE_NAME',f.value as 'TABLE_COMMENT',a.[name] as 'COLUMN_NAME',a.length '长度',c.[name] 'DATA_TYPE',e.value as 'COLUMN_COMMENT',
COLUMNPROPERTY(a.id,a.name,'IsIdentity') as 'COLUMN_IDENTITY',
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 = a.id AND colid=a.colid ))) then 1 else 0 end as 'COLUMN_PRIMARYKEY'
FROM syscolumns a left join systypes b on a.xusertype=b.xusertype
left join systypes c on a.xtype = c.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U'
left join sys.extended_properties e on a.id = e.major_id and a.colid = e.minor_id and e.name='MS_Description'
left join sys.extended_properties f on d.id = f.major_id and f.minor_id=0 and e.name='MS_Description'

 

查询外键索引关系语句

select
OBJECT_NAME(objs.parent_obj) 'FK_TBName',
(SELECT top 1 name FROM syscolumns WHERE colid=keys.fkey AND id=keys.fkeyid ) 'FK_ColumnName'
,OBJECT_NAME(keys.rkeyid) 'RK_TBName'
,(SELECT top 1 name FROM syscolumns WHERE colid=keys.rkey AND id=keys.rkeyid) 'RK_ColumnName'
from sysconstraints const
inner join sysobjects objs on const.constid=objs.id and objs.xtype ='F'
inner join sysforeignkeys keys on objs.id=keys.constid