SQL Server Objects Help

The following procedure is used for to get objects information. It executes with parameter as table name the result shows all information about table and instead of table parameter as stored procedure name then it shows the code information.And also we can use for View, Function etc.





Create Procedure [dbo].[H]
(
    @Object    Varchar(Max)
)
As
/*
    Execution : Exec H <Table Name/ SP / View / Fn>
*/

Begin
    Declare @Schema        varchar(1000)
    Declare @ObjectName    varchar(Max)

    Set @Schema = Left(@Object,Case when CharIndex('.',@Object)> 0 then CharIndex('.',@Object) else 1 end -1)
    Set @ObjectName    = SubString(@Object,CharIndex('.',@Object)+1,Len(@Object)-CharIndex('.',@Object))

    If Exists(Select TABLE_NAME From Information_Schema.Tables
                Where TABLE_SCHEMA = Case When @Schema = '' then TABLE_SCHEMA else @Schema end
                And TABLE_NAME = @ObjectName
                And Table_Type = 'BASE TABLE'
             )
        Exec SP_HELP @ObjectName
    Else
        Exec SP_HELPTEXT @ObjectName
End