Modifying SQL Stored Procedures/Functions/Views with SQL

I thought I might explain this article a bit more, backup/restore is not enough if your installation is not so simple, accessing tables from a different database can make life a bit more difficult.

I'm going to write about three major features of the other script:
1. List Programmable objects
2. Retrieving Indexes
3. Modify all programmable objects

1. List Programmable objects

Almost all databases contain one way or another of programmable objects, stored procedures, stored functions and views, some DBAs write the full object name [dbname].[schema].[object], some by shortcut [dbname]..[object], sometimes there's a need to access one database from the other, going over even 10 of these objects can be a headache and a complete waste of time, so first, lets dump them to a temp table.

-- =============================================
-- Author:      Dror Gluska
-- Create date: 2010-05-30
-- Description: Gets all Views/StoredProcedures and references outside the current database
-- =============================================
create PROCEDURE tuspGetAllExecutables
AS
BEGIN
    SET NOCOUNT ON;

    declare @retval table (name nvarchar(max), text nvarchar(max), refs int);
    
    declare @tmpval nvarchar(max);
    
    declare @tmpname nvarchar(max);
    
    declare @ref table(
                        ReferencingDBName nvarchar(255),
                        ReferencingEntity nvarchar(255),
                        ReferencedDBName nvarchar(255),
                        ReferencedSchema nvarchar(255),
                        ReferencedEntity nvarchar(255)
                    );
    declare @refdata table (DBName nvarchar(255), Entity nvarchar(255), NoOfReferences int);
                    
    insert into @ref
    select DB_NAME() AS ReferencingDBName, OBJECT_NAME(referencing_id) as ReferencingEntity, referenced_database_name, referenced_schema_name, referenced_entity_name
    FROM sys.sql_expression_dependencies


    
    
    insert into @refdata
    select ReferencingDBNAme,
           ReferencingEntity,
           SUM(NoOfReferences) as NoOfReferences
    from
    (
        select *
        ,    (
                select COUNT(*) 
                from @ref r2
                where r2.ReferencedEntity = [@ref].ReferencingEntity
                and r2.ReferencedDBName = [@ref].ReferencingDBName
            ) 
            as NoOfReferences

        from @ref
    ) as refs
    group by ReferencingDBNAme,         ReferencingEntity
    order by NoOfReferences 


    
    declare xpcursor CURSOR for
    SELECT name
    FROM syscomments B, sysobjects A
    WHERE A.[id]=B.[id]
    and xtype in ('TF','IF','P','V')
    group by name
    order by name
    
    open xpcursor
    fetch next from xpcursor into @tmpname
    
    while @@FETCH_STATUS = 0
    begin
        set @tmpval = '';
        
        select @tmpval = @tmpval + text
        FROM syscomments B, sysobjects A
        WHERE A.[id]=B.[id]
        and A.name = @tmpname
        order by colid
        
        insert into @retval
        select @tmpname, @tmpval, (select top 1 NoOfReferences from @refdata where [@refdata].Entity = @tmpname)
        
        fetch next from xpcursor into @tmpname
    end
    
    close xpcursor
    deallocate xpcursor
    
    select * from @retval
    order by refs 
    
END
GO

The output of this stored procedure looks something like this:

2. Retrieving Indexes

But that's not enough for schemabound views, since they support indexes, we need to save these indexes too since all indexes drop when schemabound views are altered.

-- =============================================
-- Author:        thorv-918308
-- Create date: 2009-06-05
-- Description:    Script all indexes as CREATE INDEX statements
-- Copied from http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx#bm879833
-- =============================================
CREATE PROCEDURE tuspGetIndexOnTable
    @indexOnTblName nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    --1. get all indexes from current db, place in temp table
    select
            tablename = object_name(i.id),
            tableid = i.id,
            indexid = i.indid,
            indexname = i.name,
            i.status,
            isunique = indexproperty (i.id,i.name,'isunique'),
            isclustered = indexproperty (i.id,i.name,'isclustered'),
            indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
    into #tmp_indexes
    from sysindexes i
    where i.indid > 0 and i.indid < 255                                             --not certain about this
    and (i.status & 64) = 0                                                                 --existing indexes


    --add additional columns to store include and key column lists
    alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
    --go
    --################################################################################################




    --2. loop through tables, put include and index columns into variables
    declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

    declare index_cursor cursor for
    select tableid, indexid from #tmp_indexes  

    open index_cursor
    fetch next from index_cursor into @tableid, @indexid

    while @@fetch_status <> -1
    begin

            select @isql_key = '', @isql_incl = ''

            select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
                    --key column
                    @isql_key = case ic.is_included_column 
                            when 0 then 
                                    case ic.is_descending_key 
                                            when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
                                            else            @isql_key + coalesce(sc.name,'') + ' ASC, '
                                    end
                            else @isql_key end,
                            
                    --include column
                    @isql_incl = case ic.is_included_column 
                            when 1 then 
                                    case ic.is_descending_key 
                                            when 1 then @isql_incl + coalesce(sc.name,'') + ', ' 
                                            else @isql_incl + coalesce(sc.name,'') + ', ' 
                                    end
                            else @isql_incl end
            from sysindexes i
            INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
            INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
            
            where i.indid > 0 and i.indid < 255
            and (i.status & 64) = 0
            and i.id = @tableid and i.indid = @indexid
            order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

            
            if len(@isql_key) > 1   set @isql_key   = left(@isql_key,  len(@isql_key) -1)
            if len(@isql_incl) > 1  set @isql_incl  = left(@isql_incl, len(@isql_incl) -1)

            update #tmp_indexes 
            set keycolumns = @isql_key,
                    includes = @isql_incl
            where tableid = @tableid and indexid = @indexid

            fetch next from index_cursor into @tableid,@indexid
            end

    close index_cursor
    deallocate index_cursor

    --remove invalid indexes,ie ones without key columns
    delete from #tmp_indexes where keycolumns = ''
    --################################################################################################

    --select * from #tmp_indexes


    --3. output the index creation scripts
    set nocount on

    --separator
    --select '---------------------------------------------------------------------'

    --create index scripts (for backup)
    SELECT  
            'CREATE ' 
            + CASE WHEN ISUNIQUE    = 1 THEN 'UNIQUE ' ELSE '' END 
            + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
            + 'INDEX [' + INDEXNAME + ']' 
            +' ON [' + TABLENAME + '] '
            + '(' + keycolumns + ')' 
            + CASE 
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN '' 
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' 
                    WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
                    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
                    ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'  
            END collate database_default as 'DDLSQL'
    FROM #tmp_indexes
    where left(tablename,3) not in ('sys', 'dt_')   --exclude system tables
    and tablename = @indexOnTblName
    order by tablename, indexid, indexname


    set nocount off

    drop table #tmp_indexes

END
GO

3. Modify all programmable objects

Now that we have a list of all the programmable objects and the indexes the schemabound objects have we can go over each object, modify it and save it.

declare @codetext nvarchar(max);
declare @newcodetext nvarchar(max);
declare @idxcode nvarchar(max)
declare @tablename nvarchar(255);
declare @indextable table (DDLSQL nvarchar(max));

declare @executables table(name nvarchar(max), text nvarchar(max),refcount int);
insert into @executables
exec tuspGetAllExecutables

declare spcursor CURSOR for
select text,name
from @executables
order by refcount

open spcursor
fetch next from spcursor into @codetext, @tablename

while @@FETCH_STATUS = 0
begin
    set @newcodetext = @codetext;
    
    set @newcodetext = REPLACE( @newcodetext,'test.','test_development.')
    
    if (@newcodetext != @codetext)
    begin
        set @newcodetext = REPLACE( @newcodetext,'CREATE FUNCTION','ALTER FUNCTION')
        set @newcodetext = REPLACE( @newcodetext,'CREATE PROCEDURE','ALTER PROCEDURE')
        set @newcodetext = REPLACE( @newcodetext,'CREATE VIEW','ALTER VIEW')
        
        insert into @indextable
        exec tuspGetIndexOnTable @tablename
                
        print @tablename
        print @newcodetext
        EXECUTE sp_executesql @newcodetext
        
        --recreate lost index for schemabinded views
        if (select COUNT(*) from @indextable) > 0
        begin
            
            declare vidxcursor cursor for select DDLSQL from @indextable
            open vidxcursor
            fetch next from vidxcursor into @idxcode
            
            while @@FETCH_STATUS = 0
            begin
                print @idxcode
                EXECUTE sp_executesql @idxcode
                fetch next from vidxcursor into @idxcode
            end
            close vidxcursor
            deallocate vidxcursor
            
        end
    end
    fetch next from spcursor into @codetext, @tablename
end

close spcursor
deallocate spcursor

So what happens here?
a. we use tuspGetAllExecutables to get all programmable objects.
b. we 'replace' all occurences of 'test.' to 'test_development.', its not perfect (or even correct for your case), but it worked for my needs since the database name I have is unique.
c. modify 'create' to 'alter' for functions, procedures and views.
d. get a list of indexes for that programmable object, only schemabound views return anything.
e. alter the programmable object.
f. recreate all the indexes.

Read More

Tags:

Dror Gluska February 02, 2011  No comment