SQLServer存储过程Print

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)