时间：2021-07-01 10:21:17 帮助过：144人阅读
SQLServer 存储过程，打印其他存储过程或函数对象的创建语句 代码来源于系统自带的存储过程 无 Create PROCEDURE [dbo].[sp_PrintProc] @objname NVARCHAR(776) , @columnname SYSNAME = NULLAS --打印对象 SET nocount ON DECLARE @dbname SYSNAME , @objid
SQLServer 存储过程，打印其他存储过程或函数对象的创建语句
代码来源于系统自带的存储过程
Create PROCEDURE [dbo].[sp_PrintProc]
@objname NVARCHAR(776) ,
@columnname SYSNAME = NULL
AS
--打印对象
SET nocount ON
DECLARE @dbname SYSNAME ,
@objid INT ,
@BlankSpaceAdded INT ,
@BasePos INT ,
@CurrentPos INT ,
@TextLength INT ,
@LineId INT ,
@AddOnLen INT ,
@LFCR INT --lengths of line feed carriage return
,
@DefinedLength INT
/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 2550. These need to all have
** the same values. 2550 was selected in order for the max length
** display using down level clients
*/ ,
@SyscomText NVARCHAR(4000) ,
@Line NVARCHAR(2550)
SELECT @DefinedLength = 2550
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(
LineId INT ,
Text NVARCHAR(2550) COLLATE database_default
)
/*
** 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
/*
** See if @objname exists.
*/
SELECT @objid = OBJECT_ID(@objname)
IF ( @objid IS NULL )
BEGIN
RAISERROR(15009,-1,-1,@objname,@dbname)
RETURN (1)
END
-- If second parameter was given.
IF ( @columnname IS NOT NULL )
BEGIN
-- Check if it is a table
IF ( SELECT COUNT(*)
FROM sys.objects
WHERE object_id = @objid
AND type IN ( 'S ', 'U ', 'TF' )
) = 0
BEGIN
RAISERROR(15218,-1,-1,@objname)
RETURN(1)
END
-- check if it is a correct column name
IF ( ( SELECT 'count' = COUNT(*)
FROM sys.columns
WHERE name = @columnname
AND object_id = @objid
) = 0 )
BEGIN
RAISERROR(15645,-1,-1,@columnname)
RETURN(1)
END
IF ( COLUMNPROPERTY(@objid, @columnname, 'IsComputed') = 0 )
BEGIN
RAISERROR(15646,-1,-1,@columnname)
RETURN(1)
END
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = @objid AND encrypted = 0 AND number =
(SELECT column_id FROM sys.columns WHERE name = @columnname AND object_id = @objid)
ORDER BY number,colid
FOR READ ONLY
END
ELSE
IF @objid < 0 -- Handle system-objects
BEGIN
-- Check count of rows with text data
IF ( SELECT COUNT(*)
FROM master.sys.syscomments
WHERE id = @objid
AND text IS NOT NULL
) = 0
BEGIN
RAISERROR(15197,-1,-1,@objname)
RETURN (1)
END
DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT text FROM master.sys.syscomments WHERE id = @objid
ORDER BY number, colid FOR READ ONLY
END
ELSE
BEGIN
/*
** Find out how many lines of text are coming back,
** and return if there are none.
*/
IF ( SELECT COUNT(*)
FROM syscomments c ,
sysobjects o
WHERE o.xtype NOT IN ( 'S', 'U' )
AND o.id = c.id
AND o.id = @objid
) = 0
BEGIN
RAISERROR(15197,-1,-1,@objname)
RETURN (1)
END
IF ( SELECT COUNT(*)
FROM syscomments
WHERE id = @objid
AND encrypted = 0
) = 0
BEGIN
RAISERROR(15471,-1,-1,@objname)
RETURN (0)
END
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = @objid AND encrypted = 0
ORDER BY number, colid
FOR READ ONLY
END
/*
** else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom INTO @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(CHAR(13) + CHAR(10),
@SyscomText, @BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
WHILE ( ISNULL(LEN(@Line), 0) + @BlankSpaceAdded
+ @CurrentPos - @BasePos + @LFCR ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength
- ( ISNULL(LEN(@Line), 0)
+ @BlankSpaceAdded )
INSERT #CommentText
VALUES ( @LineId,
ISNULL(@Line, N'')
+ ISNULL(SUBSTRING(@SyscomText,
@BasePos,
@AddOnLen), N'') )
SELECT @Line = NULL ,
@LineId = @LineId + 1 ,
@BasePos = @BasePos + @AddOnLen ,
@BlankSpaceAdded = 0
END
SELECT @Line = ISNULL(@Line, N'')
+ ISNULL(SUBSTRING(@SyscomText, @BasePos,
@CurrentPos - @BasePos
+ @LFCR), N'')
SELECT @BasePos = @CurrentPos + 2
INSERT #CommentText
VALUES ( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
WHILE ( ISNULL(LEN(@Line), 0)
+ @BlankSpaceAdded + @TextLength
- @BasePos + 1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength
- ( ISNULL(LEN(@Line), 0)
+ @BlankSpaceAdded )
INSERT #CommentText
VALUES ( @LineId,
ISNULL(@Line, N'')
+ ISNULL(SUBSTRING(@SyscomText,
@BasePos,
@AddOnLen), N'') )
SELECT @Line = NULL ,
@LineId = @LineId + 1 ,
@BasePos = @BasePos
+ @AddOnLen ,
@BlankSpaceAdded = 0
END
SELECT @Line = ISNULL(@Line, N'')
+ ISNULL(SUBSTRING(@SyscomText,
@BasePos,
@TextLength
- @BasePos + 1),
N'')
IF LEN(@Line) < @DefinedLength
AND CHARINDEX(' ', @SyscomText,
@TextLength + 1) > 0
BEGIN
SELECT @Line = @Line + ' ' ,
@BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom INTO @SyscomText
END
IF @Line IS NOT NULL
INSERT #CommentText
VALUES ( @LineId, @Line )
DECLARE @printLine NVARCHAR(2550)
DECLARE PostCur CURSOR FOR
SELECT Text FROM #CommentText ORDER BY LineId
OPEN PostCur
FETCH NEXT FROM PostCur INTO @printLine
WHILE @@fetch_status = 0
BEGIN
PRINT @printLine
FETCH NEXT FROM PostCur INTO @printLine
END
CLOSE PostCur
DEALLOCATE PostCur
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
RETURN (0) -- sp_PrintProc
--存储过程查询
declare @StrSql varchar(max)
set @StrSql=(
Select 'exec('+char(39)+'sp_PrintProc '+name+char(39)+');' as [data()] From sys.objects where Type='P' and name like 'softManage_%' for xml path('')
)
exec(@StrSql)