mysql sp print_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)

本文原创发布php中文网，转载请注明出处，感谢您的尊重！