CAG6n0VU

1USE [VeeamOne]
2GO
3/****** Object:  StoredProcedure [reporter].[DeleteExtensionModuleDDL]    Script Date: 02/12/2015 12:06:19 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9ALTER PROCEDURE [reporter].[DeleteExtensionModuleDDL]
10@EMID int
11AS
12BEGIN
13SET NOCOUNT ON;
14declare @Debug bit;
15set @Debug = 0;
16declare @Emulate bit;
17set @Emulate = 0;
18declare @reportPackDestructorFunctionName nvarchar(max)
19exec @reportPackDestructorFunctionName = [reporter].GenerateExtensionModuleDestructorName @EMID
20if exists(select * from sys.objects where (object_id = OBJECT_ID(@reportPackDestructorFunctionName) and type in (N'P', N'PC')))
21begin
22exec @reportPackDestructorFunctionName
23declare @objectsToDelete as table (Name nvarchar(2048), Type nvarchar(2048))
24insert @objectsToDelete exec @reportPackDestructorFunctionName
25if @Debug = 1
26begin
27select * from @objectsToDelete
28end
29declare @TablesToDelete    as table(ObjectID int, Name varchar(max))
30declare @FunctionsToDelete   as Table(Name nvarchar(max))
31declare @StoredProceduresToDelete as Table(Name nvarchar(max))
32declare @AssembliesToDelete   as Table(Name nvarchar(max))
33declare @ViewsToDelete    as Table(Name nvarchar(max))
34insert into @TablesToDelete
35select object_id(Name), Name
36from @objectsToDelete
37where Type = 'Table'
38insert into @FunctionsToDelete
39select Name
40from @objectsToDelete
41where Type = 'Function'
42insert into @StoredProceduresToDelete
43select Name
44from @objectsToDelete
45where Type = 'Procedure'
46union
47select @reportPackDestructorFunctionName
48insert into @AssembliesToDelete
49select Name
50from @objectsToDelete
51where Type = 'Assembly'
52insert into @ViewsToDelete
53select Name
54from @objectsToDelete
55where Type = 'View'
56declare @DependencyTree as Table(ForeignKeyObjectID int, ForeignKeyObjectName nvarchar(max),
57ParentTableID int, ParentTableName nvarchar(max),
58ChildTableID int, ChildTableName nvarchar(max), Generation int)
59declare @Generation int;
60set @Generation = 0;
61insert into @DependencyTree
62select  distinct(fk.object_id) as ForeignKeyObjectID, fk.name as ForeignKeyObjectName,
63fk.referenced_object_id as ParentTableID, parent.name as ParentTableName,
64fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation
65from  sys.foreign_keys as fk
66inner join sys.objects as parent
67on   fk.referenced_object_id = parent.object_id
68inner join sys.objects as child
69on   fk.parent_object_id = child.object_id
70where  fk.referenced_object_id in (select ObjectID from @TablesToDelete)
71while @@ROWCOUNT > 0
72begin
73set @Generation = @Generation + 1
74insert into @DependencyTree
75select  fk.object_id as ForeignKeyObjectID, fk.name as ForeignKeyObjectName,
76fk.referenced_object_id as ParentTableID, parent.name as ParentTableName,
77fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation
78from  @DependencyTree dt
79inner join sys.foreign_keys as fk
80on   fk.referenced_object_id = dt.ChildTableID
81inner join sys.objects as parent
82on   fk.referenced_object_id = parent.object_id
83inner join sys.objects as child
84on   fk.parent_object_id = child.object_id
85except
86select  ForeignKeyObjectID, ForeignKeyObjectName,
87ParentTableID, ParentTableName,
88ChildTableID, ChildTableName, @Generation
89from  @DependencyTree
90end
91declare @clearScript as table(ID int primary key identity (0,1), ScriptText nvarchar(max))
92insert into @clearScript
93select  'alter table [reporter].[' + ChildTableName +
94'] drop constraint [' + ForeignKeyObjectName + ']'
95from  @DependencyTree
96where  ParentTableName in (select Name from @TablesToDelete)
97insert into @clearScript
98select 'drop table [reporter].[' + Name + ']' from @TablesToDelete
99insert into @clearScript
100select 'drop function [reporter].[' + Name + ']'
101from @FunctionsToDelete
102insert into @clearScript
103select 'drop procedure [reporter].[' + Name + ']'
104from @StoredProceduresToDelete
105insert into @clearScript
106select 'drop assembly [reporter].[' + Name + ']'
107from @AssembliesToDelete
108insert into @clearScript
109select 'drop view [reporter].[' + Name + ']'
110from @ViewsToDelete
111if @Debug = 1
112begin
113select * from @clearScript
114end
115declare @str nvarchar(max)
116declare @ID int;
117set @ID  = 0;
118declare @MaxID int
119select @MaxID = MAX(ID) from @clearScript
120print ''
121while @ID <= @MaxID
122begin
123select @str = ScriptText from @clearScript where ID = @ID
124if @Emulate = 1
125print(@str)
126else
127exec sp_executesql @statement = @str
128set @ID = @ID + 1
129end
130end
131END
132	
133select NameValue COLLATE DATABASE_DEFAULT from MyDatabase.Schema.Table
134EXCEPT
135select NameValue COLLATE DATABASE_DEFAULT from TheirDatabase.Schema.Table
136	
137/* This is an interesting problem I ran across while trying to do some work in a collated database. 
138The SQLLogin column on the LoginsReference table is nvarchar(50) (yes, it should be sysname)
139When trying to see if [name] from the sys.sysusers table existed in the LoginsReference table, I got the following error:
140
141--Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.
142
143My temporary solution is to leverage implicit conversion to insert the SQLLogins FROM LoginsReference into a temp table that is properly collated, and use it for the comparison operator.
144Long term solution is to properly collate things in tables.  However, there is a lot of dynamic SQL involved and I'm worried there will be many more issues with other comparisons made in stored procedures elsewhere.  We'll see.
145*/
146--this is a script to clean out users created in testing and also reset all tables involved in testing.
147USE [_Contained Database]
148DECLARE @UUID smallint
149DECLARE @SQL nvarchar(max)
150DECLARE @loginname nvarchar (20)
151DECLARE @stop bit
152IF OBJECT_ID('tempdb..#usernames') is NOT NULL
153DROP TABLE #usernames
154CREATE TABLE #usernames
155([sqlLogin] nvarchar (50) COLLATE CATALOG_DEFAULT,)
156INSERT INTO #userNames
157SELECT [sqlLogin] FROM [dbo].[LoginsReference]
158
159
160WHILE EXISTS (SELECT TOP 1 [UID] FROM sys.sysusers WHERE [uid] BETWEEN 5 and 16000 and [name] in (SELECT [sqlLogin] FROM #usernames))
161BEGIN
162    SELECT TOP 1 @loginname = [name] FROM sys.sysusers WHERE [uid] BETWEEN 5 and 16000 and [name] in (SELECT [sqlLogin] FROM #usernames)
163    SELECT @SQL = 'DROP USER ' + @loginname
164    exec sp_executesql @sql
165END
166TRUNCATE TABLE [dbo].[LoginRoles]
167TRUNCATE TABLE [dbo].[LoginsReference]