Sql 系統資訊操作
1 導表的:
SELECT TOP (100) PERCENT
CASE WHEN a.colorder = 1 THEN d .name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '')ELSE '' END AS 表說明,
a.colorder AS 欄位序號,
a.name AS 欄位名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 THEN '√' ELSE '' END AS 標識,
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 '√' ELSE '' END AS 主鍵,
b.name AS 型別,
a.length AS 長度,
COLUMNPROPERTY(a.id, a.name,'PRECISION') AS 精度,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空,
ISNULL(e.text, '') AS 預設值,
ISNULL(g.value, '') AS 欄位說明,
d.crdate AS 建立時間,
CASE WHEN a.colorder = 1 THEN d .refdate ELSE NULL END AS 更改時間
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
ORDER BY d.name, 欄位序號
-------------------------------------------------------------------------
2\導索引的
SELECT TOP 100 PERCENT --a.id,
CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名,
CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名稱,
d.name AS 列名,
b.keyno AS 索引順序,
CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序,
CASE WHEN p.id IS NULL THEN '' ELSE '√' END AS 主鍵,
CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集,
CASE INDEXPROPERTY(c.id,a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一,
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一約束,
a.OrigFillFactor AS 填充因子,
c.crdate AS 建立時間,
c.refdate AS 更改時間
FROM dbo.sysindexes a INNER JOIN
dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id,
N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno
------------------------------------------------------------------------
3\匯出主\外來鍵\約束\試圖等物件
SELECT DISTINCT
TOP 100 PERCENT o.xtype,
CASE o.xtype WHEN 'X' THEN '擴充套件儲存過程' WHEN 'TR' THEN '觸發器' WHEN 'PK' THEN
'主鍵' WHEN 'F' THEN '外來鍵' WHEN 'C' THEN '約束' WHEN 'V' THEN '檢視' WHEN 'FN'
THEN '函式-標量' WHEN 'IF' THEN '函式-內嵌' WHEN 'TF' THEN '函式-表值' ELSE '儲存過程'
END AS 型別, o.name AS 物件名, o.crdate AS 建立時間, o.refdate AS 更改時間,
c.text AS 宣告語句
FROM dbo.sysobjects o LEFT OUTER JOIN
dbo.syscomments c ON o.id = c.id
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)
ORDER BY CASE o.xtype WHEN 'X' THEN '擴充套件儲存過程' WHEN 'TR' THEN '觸發器' WHEN
'PK' THEN '主鍵' WHEN 'F' THEN '外來鍵' WHEN 'C' THEN '約束' WHEN 'V' THEN '檢視'
WHEN 'FN' THEN '函式-標量' WHEN 'IF' THEN '函式-內嵌' WHEN 'TF' THEN '函式-表值'
ELSE '儲存過程' END DESC
這是3個都是匯出這個庫中的相應物件,如果想匯出單個表的,需要你自己修改一下
希望對你有幫助
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17140602/viewspace-582330/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【sql調優】系統資訊統計SQL
- CRM系統實現資訊共享如何操作
- 學生資訊管理系統之SQL連結SQL
- Solaris 與系統資訊有關的操作命令(轉)
- SQL Server中常用全域性變數和系統資訊SQLServer變數
- SQL Server 更新統計資訊SQLServer
- 【統計資訊】全面檢視錶所有統計資訊SQLSQL
- 資訊系統
- Oracle系統統計資訊Oracle
- 收集統計資訊的簡單操作
- win10修改系統oem資訊的方法_win10系統改oem的操作方法Win10
- 資料庫系統操作規範及SQL書寫建議資料庫SQL
- 資訊系統監理之資訊系統專案管理專案管理
- Linux系統資訊Linux
- 資訊系統安全
- 資訊系統解析
- 資訊系統治理
- 資訊系統工程
- 【SCRIPT】Oracle統計資訊相關SQLOracleSQL
- MS SQL Server 2000 系統資料庫及相關資訊SQLServer資料庫
- SQL Server 2000系統DTS遷移後需要手工生成優化統計資訊 ?SQLServer優化
- MySQL系統如何收集統計資訊MySql
- 系統統計資訊system_pkg
- SQL優化之統計資訊和索引SQL優化索引
- "好的"資訊系統
- 資訊系統考勤員
- 資訊系統好比衣服
- 資訊系統架構架構
- java 獲得系統資訊Java
- 資訊系統分類
- 資訊系統設計
- 資訊源管理系統是資訊部門自身資訊化
- 資訊系統的輔助應用系統
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- 資訊系統應用的配套制度建設 三大資訊系統
- 學生資訊管理系統(二)刪除資訊
- Go 系統命令管道操作Go