由于最近工作涉及SQL语句较多，对一些不常见的SQL函数、及存储过程下面进行整理和描述，供大家分享：
/************************************************************************************************************** 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 表