...
SQL獲取資料庫名,表名,列名,說明等信息 轉自漢城節度使 http://www.cnblogs.com/tuqun/p/3724000.html
/************************************************************************************************************** 1、獲取所有用戶名: islogin='1' :表示帳戶 islogin='0' :表示角色 status='2' :表示用戶帳戶 status='0' :表示系統帳戶 **************************************************************************************************************/ SELECT name FROM sysusers WHERE status = '0' AND islogin = '1' /************************************************************************************************************** 2、獲取所有資料庫名: **************************************************************************************************************/ SELECT name FROM master..sysdatabases ORDER BY name /************************************************************************************************************** 3、獲取所有表名: XType='U' :表示所有用戶表; XType='S' :表示所有系統表; **************************************************************************************************************/ SELECT name FROM 資料庫名..sysobjects WHERE xtype = 'U' ORDER BY name /************************************************************************************************************** 4、獲取所有欄位名: **************************************************************************************************************/ SELECT name FROM syscolumns WHERE id = Object_id('表名') /************************************************************************************************************** 5、獲取資料庫所有類型: **************************************************************************************************************/ SELECT name FROM systypes /************************************************************************************************************** 6、獲取主鍵欄位: **************************************************************************************************************/ SELECT name FROM syscolumns WHERE id = Object_id('表名') AND colid = (SELECT TOP 1 keyno FROM sysindexkeys WHERE id = Object_id('表名')) /************************************************************************************************************** 7、獲取表欄位的基本信息: a. **************************************************************************************************************/ SELECT 欄位名 = Rtrim(b.name) ,主鍵 = CASE WHEN h.id IS NOT NULL THEN 'PK' ELSE '' END ,欄位類型 = Type_name(b.xusertype) + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(VARCHAR,Ident_seed(a.name)) + ',' + CONVERT(VARCHAR,Ident_incr(a.name)) + ')]' ELSE '' END ,長度 = b.length ,允許空 = CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END ,預設值 = Isnull(e.TEXT,'') ,欄位說明 = Isnull(c.VALUE,'') FROM sysobjects a INNER JOIN sys.all_objects aa ON a.id=aa.object_id AND schema_name(schema_id)='dbo' ,syscolumns b LEFT OUTER JOIN sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN syscomments e ON b.cdefault = e.id LEFT OUTER JOIN (SELECT g.id ,g.colid FROM sysindexes f ,sysindexkeys g WHERE (f.id = g.id) AND (f.indid = g.indid) AND (f.indid > 0) AND (f.indid < 255) AND (f.status & 2048) <> 0) h ON (b.id = h.id) AND (b.colid = h.colid) WHERE (a.id = b.id) AND (a.id = Object_id('表名')) --要查詢的表改成你要查詢表的名稱 ORDER BY b.colid /************************************************************************************************************** b. **************************************************************************************************************/ 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.xusertype = b.xusertype INNER JOIN sysobjects d ON (a.id = d.id) AND (d.xtype = 'U') AND (d.name <> 'dtproperties') INNER JOIN sys.all_objects c ON d.id=c.object_id AND schema_name(schema_id)='dbo' 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 /*************************************************************************************************************** c. 前兩種不完整~ ****************************************************************************************************************/ -- 資料庫中所有資料表:透過內定的Stored Procedure sp_tables EXEC sp_tables @table_name = '%',@table_owner = 'dbo',@table_qualifier = @DBName; -- 取得資料表Schema EXEC sp_columns @TableName -- 取得欄位說明、備註(Extended Property):這個是透過Sql內定的Function來處理 SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default) -- 取得主索引 EXEC sp_pkeys @TableName /************************************************************************************************************** 表及欄位描述信息處理示例 **************************************************************************************************************/ --創建表 CREATE TABLE 表 ( a1 VARCHAR(10) ,a2 CHAR(2)) --為表添加描述信息 EXECUTE Sp_addextendedproperty N'MS_Description' , '人員信息表' , N'user' , N'dbo' , N'table' , N'表' , NULL , NULL EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'人員信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表' --為欄位a1添加描述信息 EXECUTE Sp_addextendedproperty N'MS_Description' , '姓名' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a1' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'人員信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表', @level2type=N'COLUMN',@level2name=N'a1' --為欄位a2添加描述信息 EXECUTE Sp_addextendedproperty N'MS_Description' , '性別' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a2' --更新表中列a1的描述屬性: EXEC Sp_updateextendedproperty 'MS_Description' , '欄位1' , 'user' , dbo , 'table' , '表' , 'column' , a1 --顯示表的描述屬性 SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table','表','column',NULL) --刪除表中列a1的描述屬性: EXEC Sp_dropextendedproperty 'MS_Description' , 'user' , dbo , 'table' , '表' , 'column' , a1 --刪除測試 DROP TABLE 表