05
2012
08

sql系统中的存储过程,从中可以找到查询各种信息的语句

create procedure sys.sp_help    

 @objname nvarchar(776) = NULL  -- object name we're after    

as    

 -- PRELIMINARY    

 set nocount on    

 declare @dbname sysname    

  ,@no varchar(35), @yes varchar(35), @none varchar(35)    

 select @no = 'no', @yes = 'yes', @none = 'none'    

    

 -- If no @objname given, give a little info about all objects.    

 if @objname is null  ----传入空值  

 begin    

  -- DISPLAY ALL SYSOBJECTS --    

        select    

            'Name'          = o.name,    

            'Owner'         = user_name(ObjectProperty( object_id, 'ownerid')),    

            'Object_type'   = substring(v.name,5,31)    

        from sys.all_objects o, master.dbo.spt_values v    

        where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'    

        order by [Owner] asc, Object_type desc, Name asc    

    

  print ' '    

    

  -- DISPLAY ALL USER TYPES    

  select    

   'User_type' = name,    

   'Storage_type' = type_name(system_type_id),    

   'Length'  = max_length,    

   'Prec'  = Convert(int,TypePropertyEx(user_type_id, 'precision')),    

   'Scale'  = Convert(int,TypePropertyEx(user_type_id, 'scale')),    

   'Nullable'  = case when is_nullable = 1 then @yes else @no end,    

   'Default_name' = isnull(object_name(default_object_id), @none),    

   'Rule_name'  = isnull(object_name(rule_object_id), @none),    

   'Collation'  = collation_name    

  from sys.types    

  where user_type_id > 256    

  order by name    

    

  return(0)    

 end    

    

 -- Make sure the @objname is local to the current database.    

 select @dbname = parsename(@objname,3)    

 if @dbname is null    

  select @dbname = db_name()    

 else if @dbname <> db_name()    

  begin    

   raiserror(15250,-1,-1)    

   return(1)    

  end    

    

 -- @objname must be either sysobjects or systypes: first look in sysobjects    

 declare @objid int    

 declare @sysobj_type char(2)    

 select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)    

    

 -- IF NOT IN SYSOBJECTS, TRY SYSTYPES --    

 if @objid is null    

 begin    

  -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME    

  select @objid = type_id(@objname)    

    

  -- IF NOT IN SYSTYPES, GIVE UP    

  if @objid is null    

  begin    

   raiserror(15009,-1,-1,@objname,@dbname)    

   return(1)    

  end    

    

  -- DATA TYPE HELP (prec/scale only valid for numerics)    

  select    

   'Type_name' = name,    

   'Storage_type' = type_name(system_type_id),    

   'Length'  = max_length,    

   'Prec'   = Convert(int,TypePropertyEx(user_type_id, 'precision')),    

   'Scale'   = Convert(int,TypePropertyEx(user_type_id, 'scale')),    

   'Nullable'   = case when is_nullable=1 then @yes else @no end,    

   'Default_name' = isnull(object_name(default_object_id), @none),    

   'Rule_name'  = isnull(object_name(rule_object_id), @none),    

   'Collation'  = collation_name    

  from sys.types    

  where user_type_id = @objid    

    

  return(0)    

 end    

    

 -- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO    

 select    

  'Name'    = o.name,    

  'Owner'    = user_name(ObjectProperty( object_id, 'ownerid')),    

        'Type'              = substring(v.name,5,31),    

  'Created_datetime' = o.create_date    

 from sys.all_objects o, master.dbo.spt_values v    

 where o.object_id = @objid and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'    

    

 print ' '    

    

 -- DISPLAY COLUMN IF TABLE / VIEW    

 if exists (select * from sys.all_columns where object_id = @objid)    

 begin    

    

  -- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE    

  declare @numtypes nvarchar(80)    

  select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'    

    

  -- INFO FOR EACH COLUMN    

  print ' '    

  select    

   'Column_name'   = name,    

   'Type'     = type_name(user_type_id),    

   'Computed'    = case when ColumnProperty(object_id, name, 'IsComputed') = 0 then @no else @yes end,    

   'Length'     = convert(int, max_length),    

   'Prec'     = case when charindex(type_name(system_type_id), @numtypes) > 0    

          then convert(char(5),ColumnProperty(object_id, name, 'precision'))    

          else '     ' end,    

   'Scale'     = case when charindex(type_name(system_type_id), @numtypes) > 0    

          then convert(char(5),OdbcScale(system_type_id,scale))    

          else '     ' end,    

   'Nullable'    = case when is_nullable = 0 then @no else @yes end,    

   'TrimTrailingBlanks' = case ColumnProperty(object_id, name, 'UsesAnsiTrim')    

          when 1 then @no    

          when 0 then @yes    

          else '(n/a)' end,    

   'FixedLenNullInSource' = case    

      when type_name(system_type_id) not in ('varbinary','varchar','binary','char')    

       then '(n/a)'    

      when is_nullable = 0 then @no else @yes end,    

   'Collation'  = collation_name    

  from sys.all_columns where object_id = @objid    

    

  -- IDENTITY COLUMN?    

  if @sysobj_type in ('S ','U ','V ','TF') and @objid > 0    

  begin    

   print ' '    

   declare @colname sysname    

   select @colname = col_name(@objid, column_id) from sys.identity_columns where object_id = @objid    

   select    

    'Identity'    = isnull(@colname,'No identity column defined.'),    

    'Seed'    = ident_seed(@objname),    

    'Increment'   = ident_incr(@objname),    

    'Not For Replication' = ColumnProperty(@objid, @colname, 'IsIDNotForRepl')    

   -- ROWGUIDCOL?    

   print ' '    

   select @colname = null    

   select @colname = name from sys.columns where object_id = @objid and is_rowguidcol = 1    

   select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')    

  end    

 end    

    

 -- DISPLAY ANY PARAMS    

 if exists (select * from sys.all_parameters where object_id = @objid)    

 begin    

  -- INFO ON PROC PARAMS  --查询存储过程参数--object_id=object_id(@TableName)  

  print ' '    

  select    

   'Parameter_name' = name,    

   'Type'   = type_name(user_type_id),    

   'Length'   = max_length,    

   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' then precision    

        else OdbcPrec(system_type_id, max_length, precision) end,    

   'Scale'   = OdbcScale(system_type_id, scale),    

   'Param_order'  = parameter_id,    

   'Collation'   = convert(sysname, case when system_type_id in (35, 99, 167, 175, 231, 239)    

      then ServerProperty('collation') end)    

    

  from sys.all_parameters where object_id = @objid    

 end    

    

 -- DISPLAY TABLE INDEXES & CONSTRAINTS    

 if @sysobj_type in ('S ','U ')    

 begin    

  print ' '    

  EXEC sys.sp_objectfilegroup @objid    

  print ' '    

  EXEC sys.sp_helpindex @objname    

  print ' '    

  EXEC sys.sp_helpconstraint @objname,'nomsg'    

  if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0    

  begin    

   raiserror(15647,-1,-1,@objname) -- No views with schemabinding reference table '%ls'.    

  end    

  else    

  begin    

            select distinct 'Table is referenced by views' = obj.name from sys.objects obj, sysdepends deps    

    where obj.type ='V' and obj.object_id = deps.id and deps.depid = @objid    

     and deps.deptype = 1 group by obj.name    

    

  end    

 end    

 else if @sysobj_type in ('V ') and @objid > 0    

 begin    

  -- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID    

  print ' '    

  raiserror(15469,-1,-1,@objname) -- No constraints defined    

  print ' '    

  raiserror(15470,-1,-1,@objname) -- No foreign keys reference table '%ls'.    

  EXEC sys.sp_helpindex @objname    

 end    

    

 return (0) -- sp_help    

  

sp_help null  




版权声明:
作者:真爱无限 出处:http://www.pukuimin.top 本文为博主原创文章版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接.
« 上一篇下一篇 »

相关文章:

评论列表:

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。