SQLServer2008/2005 生成資料字典SQL語句
以前用那個SQL語句來生成那個SQLServer庫的資料字典,在SQL2000下用的挺好的,最近裝上了那個SQL2008來研究,
突然發現不能在2008下用了,查了查資料,發現2008下有一些改動
貼上來留個記號
SQLServer2008/2005 生成資料字典語句
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->SELECT
表名=case when a.colorder=1 then d.name else '' end,
表說明=case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號=a.colorder,
欄位名=a.name,
標識=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
型別=b.name,
佔用位元組數=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
預設值=isnull(e.text,''),
欄位說明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
--where d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by a.id,a.colorder
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->SELECT
表名=case when a.colorder=1 then d.name else '' end,
表說明=case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號=a.colorder,
欄位名=a.name,
標識=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
型別=b.name,
佔用位元組數=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
預設值=isnull(e.text,''),
欄位說明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
--where d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by a.id,a.colorder
以前的SQL2000下的語句
就改動了個sysproperties
Code
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->SELECT
表名=case when a.colorder=1 then d.name else '' end,
表說明=case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號=a.colorder,
欄位名=a.name,
標識=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
型別=b.name,
佔用位元組數=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
預設值=isnull(e.text,''),
欄位說明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by a.id,a.colorder
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->SELECT
表名=case when a.colorder=1 then d.name else '' end,
表說明=case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號=a.colorder,
欄位名=a.name,
標識=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
型別=b.name,
佔用位元組數=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
預設值=isnull(e.text,''),
欄位說明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by a.id,a.colorder
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-594319/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 匯出Sql Server資料字典的語句SQLServer
- SQL Server 2000/2005資料字典生成方法SQLServer
- SQL語句資料SQL
- 【原】獲取SQLServer的最完整資料字典的SQL語句SQLServer
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- 【資料庫】SQL語句資料庫SQL
- sql 2005 返回列描述的SQL語句SQL
- (轉)Oracle常用資料字典查詢語句Oracle
- 資料庫常用sql 語句資料庫SQL
- 資料庫SQL拼接語句資料庫SQL
- SQL語句批量插入資料SQL
- 1.4 資料庫和常用SQL語句(正文)——MySQL資料庫命令和SQL語句資料庫MySql
- 生成oracle資料字典Oracle
- mysql資料庫語句自動生成MySql資料庫
- SQL語句圖表生成工具ChartSQLSQL
- 資料庫常用的sql語句大全--sql資料庫SQL
- 資料庫常用操作SQL語句資料庫SQL
- SQL資料庫連線語句SQL資料庫
- Sql Server系列:資料控制語句SQLServer
- navicat生成mysql資料字典MySql
- Oracle學習系列—資料庫優化—Analyze語句和資料字典Oracle資料庫優化
- 定時生成分月表sql語句SQL
- jsqlparser使用記錄---生成sql語句JSSQL
- 根據DELTA自動生成SQL語句SQL
- Oracle 資料庫監控SQL語句Oracle資料庫SQL
- 用SQL語句匯入excel資料SQLExcel
- Oracle表部分資料提取SQL語句OracleSQL
- 【IT愛好者】SQL Server自動生成INSERT語句(在SQL2005下測試通過)SQLServer
- lavavel 自動生成資料字典
- 【MySQL】經典資料庫SQL語句編寫練習題——SQL語句掃盲MySql資料庫
- sql server 2005下奇怪的Delete Top 語句(downmoon)SQLServerdelete
- 資料庫常用的sql語句彙總資料庫SQL
- MYSQL---SQL語句的資料快取MySql快取
- SQL Server之資料庫語句優化SQLServer資料庫優化
- 資料庫巡檢常用的SQL語句資料庫SQL
- SQLServer資料庫管理的常用SQL語句SQLServer資料庫
- SQL Server 之資料庫語句優化SQLServer資料庫優化
- sql 正則替換資料庫語句!SQL資料庫