前言： 在ORACLE数据库的SQL*PLUS里面有个DES(DESCRIBE)命令,它可以返回数据库所存储对象的描述,如下所示
SQL> DESC STUDENT_SCORE
Name Type Nullable Default Comments
---------------- ---------- -------- ------- --------
STUDENT_NO NUMBER(10) 学号
CHINESE_SCORE NUMBER Y 语文成绩
ENGLISH_SCORE NUMBER Y 英语成绩
MATH_SOCRE NUMBER Y 数学成绩
PHYSICAL_SCORE NUMBER Y 物理成绩
SPORTS_SCORE NUMBER Y 体育成绩
CHEMICAL_SCORE NUMBER Y 化学成绩
BIOLOGICAL_SCORE NUMBER Y 生物成绩DESC可以获取表、视图等的字段名、字段类型、以及字段注释等信息。在开发过程中，这个命令非常实用，方便，也是使用频率比较高的命令，在MS SQL中没有这个命令，倒是有个sp_help命令，也比较方便，获取的信息甚至比DESC命令还多，但是它有个缺陷，不能获取字段的注释信息，有时候给你来一堆你不想关注的信息。下面我们我们来创建一个存储过程，模拟实现DESC命令的功能以及定制一些你想要的功能。希望这个存储过程能方便大家的工作。初版代码如下：
sp_desc
SETANSI_NULLSON;
GO
SETQUOTED_IDENTIFIERON
GO
IF EXISTS(SELECT 1 FROMsysobjectsWHEREid=OBJECT_ID(N'sp_desc')
ANDOBJECTPROPERTY(id,'IsProcedure')=1)
DROPPROCEDUREsp_desc;
GO
--==================================================================================================
-- ProcedureName : sp_desc
-- Author : Kerry
-- CreateDate : 2013-05-13
-- Blog : www.cnblogs.com/kerrycode/
-- Description : 模仿ORACLE的SQLPLUS命令DESC，并且参考sp_help相关
-- 增强功能
/***************************************************************************************************
Parameters : 参数说明
****************************************************************************************************
@ObjName : 需要查看的对象名称，例如表名、视图等
****************************************************************************************************
Modified Date Modified User Version Modified Reason
****************************************************************************************************
2013-05-19 Kerry V01.00.01 增加Print信息，提示输出内容
***************************************************************************************************/
--==================================================================================================
CREATEPROCEDUREsp_desc
(
@ObjName VARCHAR(32)
)
AS
SETNOCOUNTON;
DECLARE@ObjectId INT;
DECLARE@Sysobj_Type CHAR(2);
IF@ObjNameISNULL
BEGIN
PRINT'you must assign the parameter @ObjNam';
RETURN 0;
END
SELECT@ObjectId=object_id,@Sysobj_Type=typeFROMsys.all_objects
WHEREobject_id=OBJECT_ID(@ObjName);
IF@Sysobj_Type='U'AND@ObjectId> 0
BEGIN
SELECTN'************表的功能描述信息**********'ASN'表的功能描述信息';
--表的功能描述信息
SELECT ISNULL(value,'麻烦补齐表的功能描述信息')ASTable_Desc
FROM sys.extended_properties
WHERE major_id=@ObjectId
AND minor_id = 0
SELECTN'************表结构基本信息************'ASN'表结构基本信息';
--列出表结构的基本信息
SELECT C.NameASColumn_Nam,
CASEWHENT.Name='nvarchar'
THENT.name+'('+CAST(C.max_length/ 2 ASVARCHAR)+')'
ELSET.name
ENDASData_Type,
CASEWHENC.Max_Length=-1 THEN'Max'
ELSECAST(C.Max_LengthASVARCHAR)
ENDASMax_Length,
C.Precision,
C.Scale ,
CASEWHENC.is_nullable= 0 THEN'×'
ELSE'√'
ENDASIs_Nullable,
ISNULL(CAST(I.seed_valueASVARCHAR)+'-'
+CAST(I.increment_valueASVARCHAR),'')ASIs_Identity,
ISNULL(M.text,'')ASDefault_Value,
ISNULL(P.value,'')ASColumn_Comments
FROM sys.columnsC
INNERJOINsys.typesTONC.system_type_id=T.user_type_id
LEFT JOINdbo.syscommentsMONM.id=C.default_object_id
LEFT JOINsys.extended_propertiesPONP.major_id=C.object_id
ANDC.column_id=P.minor_id
LEFT JOINsys.identity_columnsIONI.column_id=C.column_id
ANDC.object_id=I.object_id
WHERE C.[object_id]=@ObjectId
ORDERBYC.Column_IdASC;
SELECTN'**********表约束基本信息************'ASN'表约束基本信息';
--表的约束信息
SELECT name,
type
FROM sys.objects
WHERE parent_object_id=@ObjectId
ANDtypeIN('C ','PK','UQ','F ','D ');
SELECTN'********表的索引基本信息********'ASN'表的索引基本信息';
--±表的索引信息
SELECT i.index_id,
i.data_space_id,
i.name,
CASEWHENtype= 0 THEN'堆'
WHENtype= 1 THEN'聚集索引'
WHENtype= 2 THEN'非聚集索引'
WHENtype= 3 THEN'XML'
WHENTYPE= 4 THEN'空间'
ENDAS[type],
i.ignore_dup_key,
i.is_unique,
i.is_hypothetical,
i.is_primary_key,
i.is_unique_constraint,
s.auto_created,
s.no_recompute
FROM sys.indexesi
JOINsys.statssONi.object_id=s.object_id
ANDi.index_id=s.stats_id
WHERE i.object_id=@ObjectId;
SELECTN'********索引包含那些字段********'AS'索引字段信息';
SELECT d.name,i.index_id,c.name
FROM sys.indexesd
INNERJOIN sys.index_columnsiONd.object_id=i.object_id
LEFTJOINsys.columnscONi.object_id=c.object_id
ANDi.index_column_id=c.column_id
WHERE d.object_id=@ObjectId;
SELECTN'********表的触发器基本信息********'ASN'触发器信息';
--表的触发器信息
SELECT trigger_name=name,
trigger_owner=USER_NAME(OBJECTPROPERTY(object_id,'ownerid')),
isupdate=OBJECTPROPERTY(object_id,'ExecIsUpdateTrigger'),
isdelete=OBJECTPROPERTY(object_id,'ExecIsDeleteTrigger'),
isinsert=OBJECTPROPERTY(object_id,'ExecIsInsertTrigger'),
isafter=OBJECTPROPERTY(object_id,'ExecIsAfterTrigger'),
isinsteadof=OBJECTPROPERTY(object_id,'ExecIsInsteadOfTrigger'),
trigger_schema=SCHEMA_NAME(schema_id)
FROM sys.objects
WHERE parent_object_id=@ObjectId
ANDtypeIN('TR','TA');
END
ELSEIF@Sysobj_Type='V'AND@ObjectId> 0
BEGIN
SELECTN'*********视图的功能描述信息**********' ASN'视图的功能描述信息';
--视图的功能描述信息
SELECT ISNULL(value,N'麻烦补齐描述该视图功能的信息')ASView_Desc
FROM sys.extended_properties
WHERE major_id=@ObjectId
AND minor_id = 0
SELECT'*************视图基本信息*****************'ASN'视图基本信息';
SELECT C.NameASColumn_Nam,
CASEWHENT.Name='nvarchar'
THENT.name+'('+CAST(C.max_length/ 2 ASVARCHAR)+')'
ELSET.name
ENDASData_Type,
CASEWHENC.Max_Length=-1 THEN'Max'
ELSECAST(C.Max_LengthASVARCHAR)
ENDASMax_Length,
C.Precision,
C.Scale ,
CASEWHENC.is_nullable= 0 THEN'×'
ELSE'√'
ENDASIs_Nullable,
ISNULL(CAST(I.seed_valueASVARCHAR)+'-'
+CAST(I.increment_valueASVARCHAR),'')ASIs_Identity,
ISNULL(M.text,'')ASDefault_Value,
ISNULL(P.value,'')ASColumn_Comments
FROM sys.columnsC
INNERJOINsys.typesTONC.system_type_id=T.user_type_id
LEFT JOINdbo.syscommentsMONM.id=C.default_object_id
LEFT JOINsys.extended_propertiesPONP.major_id=C.object_id
ANDC.column_id=P.minor_id
LEFT JOINsys.identity_columnsIONI.column_id=C.column_id
ANDC.object_id=I.object_id
WHERE C.[object_id]=@ObjectId
ORDERBYC.Column_IdASC;
SELECT'**********视图脚本***********'AS'视图脚本';
EXECsp_helptext @ObjName;
END
ELSEIF@Sysobj_Type='P'AND@ObjectId> 0
BEGIN
SELECTN'*********描述存储过程功能信息**********' ASN'描述存储过程功能信息';
--存储过程的功能描述信息
SELECT ISNULL(value,N'麻烦补齐描述该存储过程功能的信息')ASView_Desc
FROM sys.extended_properties
WHERE major_id=@ObjectId
ANDminor_id= 0;
EXECsp_help @ObjName;
END
ELSEIF@Sysobj_TypeIN('IF')AND@ObjectId> 0
BEGIN
SELECTN'*********描述自定义函数功能信息**********' ASN'描述自定义函数功能信息';
--描述自定义函数功能信息
SELECT ISNULL(value,N'麻烦补齐描述该自定义函数功能的信息')ASView_Desc
FROM sys.extended_properties
WHERE major_id=@ObjectId
ANDminor_id= 0;
SELECT 'Name'=o.name,
'Owner'=USER_NAME(OBJECTPROPERTY(object_id,'ownerid')),
'Object_type'=SUBSTRING(v.name, 5, 31)
FROM sys.all_objectso,
master.dbo.spt_valuesv
WHERE o.object_id=@ObjectIdAND o.type=SUBSTRING(v.name, 1, 2)COLLATEdatabase_default
ANDv.type='O9T'
ORDERBY[Owner]ASC,
Object_typeDESC,
NameASC
END
GO
接下来，我们新建一张表来看看效果如何，视图，存储过程、自定义函数就不大战篇幅去展示了，一个例子就OK了，有兴趣的，自己试试
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Employee') AND type='U')
DROP TABLE dbo.Employee;
GO
CREATE TABLE Employee
(
Employee_ID INT IDENTITY(1,1) , Employee_Name NVARCHAR(12) , Sex SMALLINT DEFAULT(1), Department_ID INT , Salary FLOAT , WorkYear INT , CONSTRAINT PK_Employee PRIMARY KEY(Employee_ID)
);
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Employee_ID'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Employee_Name'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Sex'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Department_ID'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'薪水' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Salary'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'WorkYear'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee'
CREATE TRIGGER TR_Employee_Salary ON Employee AFTER INSERTAS
DECLARE @Salary FLOAT;
SELECT @Salary = Salary FROM INSERTED; IF (@Salary < 0) BEGIN RAISERROR('The Salary Small than 0 ',10,1); ROLLBACK TRANSACTION;
END GO
CREATE VIEW V_EmployeeAS SELECT Employee_ID, Employee_Name, WorkYear FROM Employee
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Employee'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'给用户批量赋权限的存储过程' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'sp_authorize_right'执行存储过程，你可以获取表Employee的基本信息了，如下所示
