SQL Server Stored Procedure Print

SQL Server Stored Procedures, printing other stored procedures or function object creation statement code comes from the built-in stored procedures without CreatePROCEDURE [dbo]. [sp_PrintProc] @ objnameNVARCHAR (776), @ columnnameSYSNAMENULLAS -- print the object SETnocountONDECLARE @ dbnameSYSNAME, @ objid

SQL Server Stored Procedures, printing other stored procedures or function object creation statement code comes from the built-in stored procedures without Create PROCEDURE [dbo]. [sp_PrintProc] @ objname NVARCHAR (776), @ columnname SYSNAME = NULLAS -- print the object SET nocount on declare @ dbname SYSNAME, @ objid

SQL Server Stored Procedures, printing statements for creating other stored procedures or function objects
The Code comes from the stored procedure that comes with the system.

<无>

Create PROCEDURE [dbo]. [sp_PrintProc] @ objname NVARCHAR (776), @ columnname SYSNAME = NULLAS -- print the object 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 co Lumn 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. not E 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 na Me 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 = (SE LECT 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 SELEC T 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 (*) F ROM 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 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

-- Stored procedure query 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)