SQLServer存储过程Print-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

SQLServer存储过程Print

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:57 PM

printsqlserverstorageprocess

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)

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles