USE [master]
GO
IF OBJECT_ID('dbo.sp_proc_help') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_proc_help AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_proc_help
@ProcName SYSNAME = NULL,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@OnlyWithDescription BIT = 1,
@PrintSQL BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
WITH procdesc
AS (SELECT C1 AS ProcName,
C2 AS Description
FROM Util.dbo.ParseDelimitedTableColumns32('sp_CursorScript Not that we like cursors but if needed this procedure will generate a cursor declaration and loop for all columns in the given table
sp_ExecTemplate Given a stored procedure name this will declare the variables and generates the procedure call script and select the variables
sp_FK Given a table it will displays the foreign keys referencing to it (both drop and create scripts). Useful to find who is referencing to the current table at which column(s)
sp_GetStruct Given a temp or permanent table name or a valid sql statement it will script the table definition of what''s passed and script the insert sql against the found table
sp_Index Will script the primary key, unique key and indexes on the given table
sp_insert Will script the insert and select for the table including all columns
sp_join Will generate the SELECT sql including all referenced tables with left outer join. This is a good start to write a similar query.
sp_Merge Will generate a Merge statement joining on primary key columns and comparing all columns of change before update.
sp_objects It will find objects by name or schema or their type and will display information including full object definition script in a column in XML format
sp_pivot It will generate a pivot of the table on given input column sets, PIVOT TransactSQL function does not allow multi columns this one does. Execute "EXEC sp_pivot" to get help
sp_proc_find It will find objects scanning the procedure text, it will show the line numbers to the matched line and print the create definition of the found object (including commented out drop definition)
sp_Profile It will profile the given table name or sql statement by column level, it will generate a summary of population and it will store the profiled data in Temporary database, it will produce the script to query the data further
sp_Table Will script the full table creation definition including indexes, primary keys, foreign keys, triggers etc
sp_TableValues Will script the data in the table using the values clause of sql server within a select statement
sp_Text Will generate ALTER script definition for the provided object
sp_unpivot It will generate a script to return tables data in ColumnName, ColumnValue pairs (For each row of table there will be as many rows generated as the table has columns)
sp_whoio Detailed information about processes, including who is blocking who, memory consumption, locked resource description, command text, cumulative IO, CPU etc
sp_wholock Less information related all processes in grid mode compared to sp_whoio but detailes information in Messages section for blocking and blocked processes',
' ', '
') b), s1
AS (SELECT s.NAME AS SchemaName,
o.NAME AS ProcName,
ISNULL('/* ' + pd.Description + '*/
', '') + 'EXEC ' + o.name + '
' + SUBSTRING(Util.dbo.StringConcat(' ,' + p.name + ' = ' + ISNULL(DefaultValue, 'NULL') + ' -- ' + ct.ColumnType
+ CASE WHEN p.is_output = 1 THEN ' OUTPUT'
ELSE ''
END, '
'), 3, 8000) AS SQL
FROM master.sys.schemas s (NOLOCK)
INNER JOIN master.sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
OUTER APPLY (SELECT TOP 1 Description FROM Procdesc WHERE ProcName = o.NAME) pd
INNER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN master.sys.parameters p (NOLOCK) ON p.OBJECT_ID = o.OBJECT_ID
INNER JOIN master.sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
INNER JOIN master.sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
INNER JOIN master.sys.sql_modules m (NOLOCK) ON m.OBJECT_ID = o.OBJECT_ID
CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.PRECISION, p.scale, db.collation_name, db.collation_name) ct
CROSS APPLY (SELECT Util.dbo.SPParamDefault(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), p.name, p.is_output, m.definition) AS DefaultValue) uo
CROSS APPLY (SELECT CASE WHEN t.name LIKE '%char'
OR t.name = 'sysname' THEN 'NULL'
WHEN t.name = 'xml' THEN 'NULL'
WHEN t.name LIKE '%date%'
OR t.name LIKE '%time%' THEN 'NULL'
WHEN t.collation_name IS NULL
AND t.PRECISION > 0
AND t.name NOT LIKE '%date%'
AND t.name NOT LIKE '%time%' THEN 'NULL'
END AS BlankValue) bl
WHERE o.name LIKE 'sp[_]%'
AND o.OBJECT_ID <> @@PROCID
AND o.TYPE = 'P'
AND o.SCHEMA_ID = 1
AND (@ProcName = o.name
OR @ProcName IS NULL
AND ((@OnlyWithDescription = 1
AND pd.DESCRIPTION <> ''
OR @OnlyWithDescription = 0)))
GROUP BY s.name,
o.NAME,
pd.Description)
SELECT @SQL = Util.dbo.StringConcat('RETURN--------
' + SQL, '
GO
') + '
GO
'
FROM s1
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_proc_help
GO