SQLServer查询所有数据库名和表名及表结构等代码示例

admin MsSql

2024-03-01 0 723

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 c
LEFT 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\’)=0
AND OBJECT_NAME(c.object_id) = \’Table\’
ORDER
BY OBJECT_NAME(c.object_id), c.column_id

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

SELECT TBL.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 (
SELECT
name,
object_id,
index_id
FROM sys.indexes
WHERE is_primary_key = 1
) PK ON TBL.object_id = PK.object_id
LEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_id
LEFT 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 = 0
AND DSPTN.name = \’MS_Description\’
WHERE TBL.name = \’table\’
ORDER BY TBL.name, COL.column_id

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

SELECT TBL.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 (
SELECT
name,
object_id,
index_id
FROM sys.indexes
WHERE is_primary_key = 1
) PK ON TBL.object_id = PK.object_id
LEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_id
LEFT 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 = 0
AND DSPTN.name = \’MS_Description\’
WHERE TBL.name = \’table\’
ORDER BY TBL.name, COL.column_id