一.获取所有数据库名：

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