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
