Documenting user-defined functions

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 user-defined functions

Author

Topic

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-25 : 15:53:24

Okay, this does pretty much the same thing as the "Documenting stored procedures" post, except that it goes after user-defined functions. Enjoy!

/****************************************************************************************************/
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 #Func (
ID INT IDENTITY (1, 1),
Name VARCHAR (128)
)

CREATE TABLE #Param (
Proc_ID INT,
Name VARCHAR (128),
Type VARCHAR (32),
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 #Func (Name)
SELECT name
FROM sysobjects
WHERE xtype IN ('AF', 'FN', 'IF', 'TF')
ORDER BY name

INSERT #Param (Proc_ID, Name, Type, Length, Required, Direction, Sort)
SELECT
p.ID,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN ''
WHEN ((c.colorder = 0) AND (c.number = 0)) THEN ''
ELSE c.name END AS ColName,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN '<Result set - [' + c.name + ']>'
ELSE t.name END AS Type,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN NULL
ELSE c.Length END AS Length,
CASE c.colorder WHEN 0 THEN '' ELSE 'Yes' END AS Required,
CASE c.colorder WHEN 0 THEN '' ELSE 'Input' END AS Direction,
c.colorder
FROM sysobjects o
INNER JOIN #Func 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 IN ('AF', 'FN', 'IF', 'TF')
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 #Func p ON o.name = p.Name
INNER JOIN syscomments m ON m.id = o.id
WHERE o.xtype IN ('AF', 'FN', 'IF', 'TF')
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 #Func 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 [Function],
'Dependent object' AS Type,
c.Name AS Name,
c.ObjType AS [Object type],
NULL AS Length,
'' AS Required,
'' AS Direction,
NULL AS ParamSort,
c.ObjSort,
2 AS Sort
FROM #Func p
INNER JOIN #Children c ON p.ID = c.Proc_ID

UNION ALL

SELECT
p.Name AS [Function],
CASE m.Name WHEN '' THEN '<Return value>' ELSE m.Type END AS Type,
m.Name AS ParamName,
m.Type AS ParamType,
m.Length AS Length,
m.Required,
m.Direction,
m.Sort AS ParamSort,
NULL AS ObjSort,
1 AS Sort
FROM #Func p
INNER JOIN #Param m ON p.ID = m.Proc_ID

ORDER BY
[Function],
Sort,
ParamSort,
ObjSort,
Type

DROP TABLE #Func
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