SQL Server中获取数据库名、表名、字段名和字段注释的SQL语句

一.获取所有数据库名:

Select Name FROM Master.dbo.SysDatabases orDER BY Name;

go



二.获取所有表名:

获取用户表:

Select Name FROM SysObjects Where XType='U' order BY Name;


获取系统表:

Select Name FROM SysObjects Where XType='S' order BY Name;

go


备注:

--XType='U':表示所有用户表;


--XType='S':表示所有系统表;



三.获取所有字段名:


Select Name FROM SysColumns Where id=Object_Id('TableName');

go

或

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('tableName');

go


四. 获取表中主键所包含的列名:


SELECT syscolumns.name FROM syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id = object_id('table_name') AND sysobjects.xtype = 'PK' AND

sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND syscolumns.colid = sysindexkeys.colid;

go


五. 获取表中列的描述内容:

select a.name as table_name, b.name as column_name, c.value as remarks  

    from sys.tables a left join sys.columns b on a.object_id=b.object_id 

    left join sys.extended_properties c on a.object_id=c.major_id 

    where a.name='table_name' and c.minor_id<>0 and b.column_id=c.minor_id 

    and a.schema_id=(select schema_id from sys.schemas where name='dbo');

go


请使用浏览器的分享功能分享到微信等