1、查询所有数据库名

SELECT name FROM sysdatabases

2、查询当前数据库中所有表名，不用指定数据库，选中某数据库直接执行SQL就好

-- 'U':所有用户表名; 'S':所有系统表名;'V':所有视图表名SELECT name FROM sysobjects WHERE xtype='U' OR xtype='S' OR xtype='V'

3、获取指定表的主键字段

SELECT name AS 'PK' FROM SysColumns WHERE id=Object_Id('Table') AND colid=(SELECT TOP 1 keyno FROM sysindexkeys WHERE id=Object_Id('Table'))

4、查询指定表中的所有字段名

SELECT name FROM SysColumns WHERE id=Object_Id('Table_2')

5、查询指定表中的所有字段名和字段类型

SELECT sc.name,st.name FROM SysColumns sc,systypes st WHERE sc.xtype=st.xtype AND sc.id in(SELECT id from sysobjects WHERE xtype='U' AND name='Table')

6、获取表部分数据结构

SELECT c.name,c.user_type_id,c.max_length, c.is_nullable, remark = ex.value FROM sys.columns cLEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0AND OBJECT_NAME(c.object_id) = 'Table' ORDER BY OBJECT_NAME(c.object_id), c.column_id

7、SqlServer2000获取表结构详细信息

SELECTTBL.name AS '表名',CONVERT(NVARCHAR(500),DSPTN.value) AS '表说明', COL.column_id AS '字段序号', COL.name AS '字段名', CASE WHEN COL.is_identity = 1 THEN 'YES' ELSE 'NO' END AS '标识', CASE WHEN PK.object_id IS NOT NULL AND PK.index_id = COL.column_id THEN 'YES' ELSE 'N0' END AS '主键', TY.name AS '类型', CAST(COL.max_length AS VARCHAR) AS '占用字节数', CAST(COL.precision AS VARCHAR) AS '长度', CAST(COL.scale AS VARCHAR) AS '小数位数', CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS '允许空', ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS '默认值', CONVERT(NVARCHAR(500),SCOLMS.value) AS '字段说明' FROM sys.tables TBL INNER JOIN sys.columns COL ON TBL.object_id = COL.object_id LEFT JOIN sys.identity_columns IDCOL ON TBL.object_id = IDCOL.object_id AND COL.column_id = IDCOL.column_id LEFT JOIN sys.types TY ON COL.user_type_id = TY.user_type_id LEFT JOIN ( SELECTname,object_id,index_idFROM sys.indexesWHERE is_primary_key = 1 ) PK ON TBL.object_id = PK.object_idLEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_idLEFT JOIN sys.extended_properties SCOLMS ON COL.object_id = SCOLMS.major_id AND COL.column_id = SCOLMS.minor_id AND SCOLMS.name = 'MS_Description'LEFT JOIN sys.extended_properties DSPTN ON TBL.object_id = DSPTN.major_id AND DSPTN.minor_id = 0AND DSPTN.name = 'MS_Description'WHERE TBL.name = 'table'ORDER BY TBL.name, COL.column_id

8、SqlServer2000以上获取表结构详细信息

SELECTTBL.name AS '表名',CONVERT(NVARCHAR(500),DSPTN.value) AS '表说明', COL.column_id AS '序号', COL.name AS '字段名', CASE WHEN COL.is_identity = 1 THEN 'YES' ELSE 'NO' END AS '标识', CASE WHEN PK.object_id IS NOT NULL AND PK.index_id = COL.column_id THEN 'YES' ELSE 'N0' END AS '主键', TY.name AS '类型', CAST(COL.max_length AS VARCHAR) AS '占用字节数', CAST(COL.precision AS VARCHAR) AS '长度', CAST(COL.scale AS VARCHAR) AS '小数位数', CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS '允许空', ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS '默认值', CONVERT(NVARCHAR(500),SCOLMS.value) AS '字段说明' FROM sys.tables TBL INNER JOIN sys.columns COL ON TBL.object_id = COL.object_id LEFT JOIN sys.identity_columns IDCOL ON TBL.object_id = IDCOL.object_id AND COL.column_id = IDCOL.column_id LEFT JOIN sys.types TY ON COL.user_type_id = TY.user_type_id LEFT JOIN ( SELECTname,object_id,index_idFROM sys.indexesWHERE is_primary_key = 1 ) PK ON TBL.object_id = PK.object_idLEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_idLEFT JOIN sys.extended_properties SCOLMS ON COL.object_id = SCOLMS.major_id AND COL.column_id = SCOLMS.minor_id AND SCOLMS.name = 'MS_Description'LEFT JOIN sys.extended_properties DSPTN ON TBL.object_id = DSPTN.major_id AND DSPTN.minor_id = 0AND DSPTN.name = 'MS_Description'WHERE TBL.name = 'table'ORDER BY TBL.name, COL.column_id