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.
View Dror Gluska's profile