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