MSSQL 获取数据库、表、字段信息语句

 

 

--获取所有数据库名称
SELECT Name FROM Master..SysDatabases ORDER BY Name


--获取库里所有表名
SELECT * FROM SysObjects Where XType='U'
SELECT Name FROM SysObjects Where XType='U' and (Name not like 'sys%' and Name not like 'MSp%')


--获取库里表的主键
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME like 'PK_dbo%' order by TABLE_NAME


--获取库里所有表主键id及类型
SELECT tb.name tableName,t1.name columnName,t5.name jdbcType,case when  t4.id is null then 'false' else 'true' end as pkColumn,
    case when  COLUMNPROPERTY( t1.id,t1.name,'IsIdentity') = 1 then 'true' else 'false' end as  autoAdd
    ,cast(isnull(t6.value,'') as varchar(2000)) descr
FROM SYSCOLUMNS t1
left join SYSOBJECTS t2 on  t2.parent_obj = t1.id  AND t2.xtype = 'PK'
left join SYSINDEXES t3 on  t3.id = t1.id  and t2.name = t3.name 
left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indid
left join systypes  t5 on  t1.xtype=t5.xtype
left join sys.extended_properties t6   on  t1.id=t6.major_id   and   t1.colid=t6.minor_id
left join SYSOBJECTS tb  on  tb.id=t1.id
where t5.name<>'sysname' and tb.xtype = 'U' and t4.id is not null
order by tb.name, t1.colid asc