SQLServer查詢所有數(shù)據(jù)庫名和表名及表結(jié)構(gòu)等代碼示例

更新時(shí)間：2023年11月28日 08:23:55 作者：程序大白兔

SQL Server是一種關(guān)系型數(shù)據(jù)庫管理系統(tǒng),可以使用SQL語言來查詢表結(jié)構(gòu),這篇文章主要給大家介紹了關(guān)于SQLServer查詢所有數(shù)據(jù)庫名和表名及表結(jié)構(gòu)等的相關(guān)資料,文中通過代碼示例介紹的非常詳細(xì),需要的朋友可以參考下

1、查詢所有數(shù)據(jù)庫名

SELECT name FROM sysdatabases

2、查詢當(dāng)前數(shù)據(jù)庫中所有表名，不用指定數(shù)據(jù)庫，選中某數(shù)據(jù)庫直接執(zhí)行SQL就好

-- 'U':所有用戶表名; 'S':所有系統(tǒng)表名;'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、獲取表部分?jǐn)?shù)據(jù)結(jié)構(gòu)

	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獲取表結(jié)構(gòu)詳細(xì)信息

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 '標(biāo)識(shí)',
                               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 '占用字節(jié)數(shù)',
                               CAST(COL.precision AS VARCHAR) AS '長度',
                               CAST(COL.scale AS VARCHAR) AS '小數(shù)位數(shù)',
                               CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS '允許空',
                               ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS '默認(rèn)值',
                               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以上獲取表結(jié)構(gòu)詳細(xì)信息

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 '標(biāo)識(shí)',
                               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 '占用字節(jié)數(shù)',
                               CAST(COL.precision AS VARCHAR) AS '長度',
                               CAST(COL.scale AS VARCHAR) AS '小數(shù)位數(shù)',
                               CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS '允許空',
                               ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS '默認(rèn)值',
                               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

總結(jié)

到此這篇關(guān)于SQLServer查詢所有數(shù)據(jù)庫名和表名及表結(jié)構(gòu)等的文章就介紹到這了,更多相關(guān)SQLServer查詢所有表名表結(jié)構(gòu)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: