查詢表結構
查詢表中的結構資訊
一
SELECT a.[name] as '欄位名',a.length '長度',c.[name] '型別',e.value as '欄位說明' FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
left join systypes c on a.xtype = c.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U'
left join sys.extended_properties e on a.id = e.major_id and a.colid = e.minor_id and e.name='MS_Description'
where d.name='表名'
二
SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid)
THEN '1' ELSE '0' END AS zj , b.name AS lx, a.length AS cd, COLUMNPROPERTY(a.id, a.name,'PRECISION')
AS jd, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS xsws,a.isnullable AS yxk, ISNULL(e.text, '')
AS mrz, ISNULL(g.value, '') AS zdsm FROM dbo.syscolumns AS a LEFT OUTER JOIN dbo.systypes AS b ON a.xtype = b.xusertype
INNER JOIN dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN
dbo.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS g
ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN sys.extended_properties
AS f ON d.id = f.major_id AND f.minor_id = 0 where d .name='表名'
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 欄位說明對應的描述名稱(一個欄位可以新增多個不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表說明對應的描述名稱(一個表可以新增多個不同name的描述)
LEFT JOIN -- 索引及主鍵資訊
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 對於一個列包含多個索引的情況,只顯示第1個索引資訊
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
WHERE O.name=N'netzpjob' -- 如果只查詢指定表,加上此條件
ORDER BY O.name,C.column_id
相關文章
- sqlserver表結構查詢SQLServer
- SQL語句查詢表結構SQL
- 【PHP資料結構】雜湊表查詢PHP資料結構
- mysql查詢索引結構MySql索引
- 資料結構-單連結串列查詢按序號查詢資料結構
- 樹形結構的選單表設計與查詢
- Hierarchical Queries 級聯查詢(樹狀結構查詢)
- 【資料結構】折半查詢(二分查詢)資料結構
- 查詢表中的連結行
- SQL總結(二)連表查詢SQL
- 【小山】sql server通過查詢系統表得到縱向的表結構SQLServer
- 23.資料結構 查詢資料結構
- ORACLE結構化查詢語句Oracle
- NKMySQL 查詢樹結構方式gllMySql
- Oracle查詢資料表結構(欄位,型別,大小,備註)Oracle型別
- 子查詢-表子查詢
- SQL單表查詢語句總結SQL
- 使用查詢結果更新表的方法
- ORACLE遞迴查詢(適用於ID,PARENTID結構資料表)Oracle遞迴
- 資料結構實驗之查詢七:線性之雜湊表資料結構
- 重學資料結構(八、查詢)資料結構
- 資料結構之三大查詢資料結構
- PostgreSQL函式:返回表查詢結果集SQL函式
- 【資料結構】查詢結構(二叉排序樹、ALV樹、雜湊技術雜湊表)資料結構排序
- 單表查詢
- 查詢表資訊
- 根據欄位名等查詢SAP的表或結構(程式程式碼)
- 樹結構表遞迴查詢在ORACLE和MSSQL中的實現方法遞迴OracleSQL
- 模擬Oracle的desc命令編寫的describe,查詢表的結構Oracle
- Java資料結構(十五)—— 多路查詢樹Java資料結構
- Java實現遞迴查詢樹結構Java遞迴
- 樹形結構的儲存與查詢
- 資料結構 折半查詢 swift的版本資料結構Swift
- Oracle 樹形結構查詢的特殊用法Oracle
- SQL 把查詢結果當作"表"來使用SQL
- 查詢(3)--雜湊表(雜湊查詢)
- 閃回查詢之閃回表查詢
- 在查詢資料庫中,那些表在什麼時候改動結構資料庫