Documenting stored procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Documenting stored procedures

Author

Topic

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-25 : 15:51:07

Okay, this is REALLY long, but here's a procedure that lists all the stored procedures in a database, as well as the objects on which it depends. NOTE: This does NOT make use of the [sysdepends] table, which is notriously unreliable; rather, it actually parses the SQL code to find objects in the [sysobjects] table.

Here it goes ...

/**********************************************************************************************************************/
DECLARE @Proc_ID INT
DECLARE @ProcFetch INT
DECLARE @SQLCount INT
DECLARE @SQLPiece VARCHAR (8000)
DECLARE @SQL VARCHAR (8000)
DECLARE @SQLFetch INT
DECLARE @ObjName VARCHAR (128)
DECLARE @SpacePos INT
DECLARE @DotPos INT
DECLARE @ProcName VARCHAR (128)
DECLARE @MaxSQL INT

SET NOCOUNT ON

CREATE TABLE #Proc (
ID INT IDENTITY (1, 1),
Name VARCHAR (128)
)

CREATE TABLE #Param (
Proc_ID INT,
Name VARCHAR (128),
Type VARCHAR (16),
Length INT,
Required VARCHAR (3),
Direction VARCHAR (12),
Sort INT
)

CREATE TABLE #SQL (
Proc_ID INT,
SQL VARCHAR (8000),
SQLOrder TINYINT
)

CREATE TABLE #Children (
Proc_ID INT,
Name VARCHAR (128),
ObjType VARCHAR (128),
ObjSort TINYINT
)

INSERT #Proc (Name)
SELECT name
FROM sysobjects
WHERE xtype = 'P'
ORDER BY name

INSERT #Param (Proc_ID, Name, Type, Length, Required, Direction, Sort)
SELECT
p.ID,
c.name AS ColName,
t.name AS Type,
c.Length,
CASE c.IsNullable WHEN 1 THEN 'No' ELSE 'Yes' END AS Required,
CASE c.isoutparam WHEN 1 THEN 'Input/Output' ELSE 'Input' END AS Direction,
c.colorder
FROM sysobjects o
INNER JOIN #Proc p ON o.name = p.Name
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.xtype = 'P'
ORDER BY
p.ID,
c.colorder

INSERT #SQL (Proc_ID, SQL, SQLOrder)
SELECT
p.ID AS Proc_ID,
m.text AS SQL,
m.colid AS SQLOrder
FROM sysobjects o
INNER JOIN #Proc p ON o.name = p.Name
INNER JOIN syscomments m ON m.id = o.id
WHERE o.xtype = 'P'
ORDER BY
p.ID,
m.colid

DECLARE curProc CURSOR FOR
SELECT
p.ID AS Proc_ID,
p.Name AS ProcName,
MAX (s.SQLOrder) AS MaxSQL
FROM #Proc p
INNER JOIN #SQL s ON p.ID = s.Proc_ID
GROUP BY p.ID, p.Name
ORDER BY p.Name
OPEN curProc
FETCH NEXT FROM curProc INTO @Proc_ID, @ProcName, @MaxSQL
SET @ProcFetch = @@FETCH_STATUS
WHILE @ProcFetch = 0
BEGIN
SET @SQLCount = 0
SET @SQLPiece = ''

DECLARE curSQL CURSOR FOR
SELECT SQL
FROM #SQL
WHERE Proc_ID = @Proc_ID
ORDER BY SQLOrder
OPEN curSQL
FETCH NEXT FROM curSQL INTO @SQL
SET @SQLFetch = @@FETCH_STATUS
WHILE @SQLFetch = 0
BEGIN
SET @SQLCount = @SQLCount + 1

SET @DotPos = CHARINDEX ('--', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SpacePos = @DotPos
WHILE ASCII (SUBSTRING (@SQL, @SpacePos, 1)) > 31
BEGIN
SET @SpacePos = @SpacePos + 1
END

WHILE ASCII (SUBSTRING (@SQL, @SpacePos, 1)) < 32
BEGIN
SET @SpacePos = @SpacePos + 1
END

SET @SQL = LEFT (@SQL, @DotPos - 1) + LTRIM (SUBSTRING (@SQL, @SpacePos, 4000))

SET @DotPos = CHARINDEX ('--', @SQL)
END

SET @DotPos = CHARINDEX ('/*', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SpacePos = CHARINDEX ('*/', @SQL) + 2
SET @SQL = LEFT (@SQL, @DotPos - 1) + LTRIM (SUBSTRING (@SQL, @SpacePos, 8000))

SET @DotPos = CHARINDEX ('/*', @SQL)
END

SET @SQL = REPLACE (@SQL, CHAR (13), ' ')
SET @SQL = REPLACE (@SQL, CHAR (10), ' ')
SET @SQL = REPLACE (@SQL, CHAR (9), ' ')

SET @DotPos = CHARINDEX (' ', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SQL = REPLACE (@SQL, ' ', ' ')
SET @DotPos = CHARINDEX (' ', @SQL)
END

WHILE @SQL <> ''
BEGIN
SET @SpacePos = CHARINDEX (' ', @SQL)
SET @ObjName = ''

IF @SpacePos = 0
BEGIN
SET @SQLPiece = @SQLPiece + @SQL
SET @SQL = ''
IF @SQLCount < @MaxSQL
GOTO EndSQL
END
ELSE
BEGIN
SET @SQLPiece = @SQLPiece + RTRIM (LEFT (@SQL, @SpacePos))
SET @SQL = LTRIM (SUBSTRING (@SQL, @SpacePos, 8000))
END

SET @DotPos = CHARINDEX ('.', @SQLPiece)
IF @DotPos <> 0
BEGIN
SET @SpacePos = LEN (@SQLPiece)
WHILE SUBSTRING (@SQLPiece, @SpacePos, 1) <> '.'
BEGIN
SET @SpacePos = @SpacePos - 1
END

SET @SQLPiece = SUBSTRING (@SQLPiece, @SpacePos + 1, 8000)
END

SET @ObjName = @SQLPiece
SET @SQLPiece = ''

IF @ObjName <> ''
INSERT #Children (Proc_ID, Name, ObjType, ObjSort)
SELECT
@Proc_ID,
o.name,
CASE o.xtype
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'Check constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign key'
WHEN 'PK' THEN 'Primary key'
WHEN 'P' THEN 'SQL Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored procedure'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TR' THEN 'SQL DML trigger '
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'U' THEN 'Table'
WHEN 'UQ' THEN 'Unique constraint'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
WHEN 'IT' THEN 'Internal table'
END AS ObjType,
CASE
WHEN o.xtype IN ('S', 'U', 'IT') THEN 1
WHEN o.xtype IN ('UQ', 'PK', 'C', 'D', 'R') THEN 2
WHEN o.xtype = 'F' THEN 3
WHEN o.xtype = 'V' THEN 4
WHEN o.xtype IN ('X', 'P', 'PC') THEN 5
WHEN o.xtype IN ('FN', 'FS', 'FT', 'AF', 'IF', 'TF') THEN 6
WHEN o.xtype IN ('RF', 'SN', 'SQ', 'TA', 'TR') THEN 7
END AS ObjSort
FROM sysobjects o
WHERE o.name = @ObjName
AND o.name <> @ProcName
END
EndSQL:
FETCH NEXT FROM curSQL INTO @SQL
SET @SQLFetch = @@FETCH_STATUS
END
CLOSE curSQL
DEALLOCATE curSQL

FETCH NEXT FROM curProc INTO @Proc_ID, @ProcName, @MaxSQL
SET @ProcFetch = @@FETCH_STATUS
END
CLOSE curProc
DEALLOCATE curProc

SELECT DISTINCT
p.Name AS ProcName,
'Dependent object' AS Type,
c.Name AS Name,
c.ObjType AS ObjType,
NULL AS Length,
'' AS Required,
'' AS Direction,
'' AS Descr,
NULL AS ParamSort,
c.ObjSort,
2 AS Sort
FROM #Proc p
INNER JOIN #Children c ON p.ID = c.Proc_ID

UNION ALL

SELECT
p.Name AS ProcName,
'Parameter' AS Type,
m.Name AS ParamName,
m.Type AS ParamType,
m.Length AS Length,
m.Required,
m.Direction,
CASE m.Name
WHEN '@Server' THEN 'Comma delimited list of servers to examine. If omitted, all servers listed in [URLookup_Server] with an [Active] value of 1 are included.'
WHEN '@DBName' THEN 'Comma delimited list of databases to examine. If omitted, all databases listed in [URLookup_Database] associated with the servers specified by the {@Server} paramaeter are included.'
WHEN '@Drive' THEN 'Comma delimited list of drives to examine. If omitted, all drives listed in [URLookup_Drive] associated with the servers specified by the {@Server} paramaeter are included.'
WHEN '@FileType' THEN 'Comma delimited list of file types to examine. If omitted, all file types listed in [URLookup_FileType] associated with the databases specified by the {@DBName} paramaeter are included.'
WHEN '@FileName' THEN 'Comma delimited list of files to examine. If omitted, all files listed in [URLookup_FileName] associated with the databases specified by the {@DBName} paramaeter are included.'
WHEN '@StartDate' THEN 'The earliest date of the date range to be examined. If omitted, the earliest date associated with the selection as filtered by the other parameters is used.'
WHEN '@EndDate' THEN 'The latest date of the date range to be examined. If omitted, the latest date associated with the selection as filtered by the other parameters is used.'
WHEN '@NumOfUnits' THEN 'The number of time units as defined by the {@UnitType} parameter. If omitted, this parameter is ignored.'
WHEN '@UnitType' THEN 'Code representing the granularity of time to be used (refer to Appendix A: UnitType codes).'
WHEN '@IsSnapshot' THEN 'Results represent the only the latest data as defined by the {@EndDate} parameter. Default is TRUE.'
WHEN '@ByServer' THEN 'Indicates that server names are to be used as part of the results grouping. Default is TRUE.'
WHEN '@ByDrive' THEN 'Indicates that drive letters are to be used as part of the results grouping. Default is FALSE.'
WHEN '@ByDatabase' THEN 'Indicates that database names are to be used as part of the results grouping. Default is TRUE.'
WHEN '@ByFileType' THEN 'Indicates that file types are to be used as part of the results grouping. Default is FALSE.'
WHEN '@ByFileName' THEN 'Indicates that file names are to be used as part of the results grouping. Default is FALSE.'
WHEN '@IsSub' THEN 'Indicates that results are non-verbose; that is, entities (servers, drives, etc.) are returned as keys to the corresponding lookup tables. Default is FALSE.'
WHEN '@Batch_ID' THEN 'The data collection batch with which any data requests are associated.'
ELSE ''
END AS Descr,
m.Sort AS ParamSort,
NULL AS ObjSort,
1 AS Sort
FROM #Proc p
INNER JOIN #Param m ON p.ID = m.Proc_ID

ORDER BY
ProcName,
Sort,
ParamSort,
ObjSort,
Type

DROP TABLE #Proc
DROP TABLE #Param
DROP TABLE #Children
DROP TABLE #SQL

SET NOCOUNT OFF
/**********************************************************************************************************************/

I geek, therefore I am

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources