· 8 years ago · Feb 14, 2018, 11:58 AM
1ALTER PROCEDURE CheckHasGuidProc
2AS
3BEGIN
4
5if exists (select name from sys.objects where name = 'CheckHasGuidA')
6DROP TABLE CheckHasGuidA
7CREATE TABLE CheckHasGuidA (
8 dbname varchar(100),
9 tblname varchar(100),
10 hasguid varchar(100)
11);
12
13if exists (select name from sys.objects where name = 'CheckHasGuidB')
14DROP TABLE CheckHasGuidB
15CREATE TABLE CheckHasGuidB (
16 dbname varchar(100),
17 tblname varchar(100),
18 hasguid varchar(100)
19);
20
21if exists (select name from sys.objects where name = 'CheckHasGuidX')
22DROP TABLE CheckHasGuidX
23CREATE TABLE CheckHasGuidX (
24 dbname varchar(100),
25 tblname varchar(100),
26 hasguid varchar(100)
27);
28
29if exists (select name from sys.objects where name = 'CheckHasGuidY')
30DROP TABLE CheckHasGuidY
31CREATE TABLE CheckHasGuidY (
32 dbname varchar(100),
33 tblname varchar(100),
34 hasguid varchar(100)
35);
36
37if exists (select name from sys.objects where name = 'CheckHasGuid')
38DROP TABLE CheckHasGuid
39CREATE TABLE CheckHasGuid (
40 dbname varchar(100),
41 tblname varchar(100),
42 hasguid varchar(100)
43);
44
45if exists (select name from sys.objects where name = 'CheckHasGuidAll')
46DROP TABLE CheckHasGuidAll
47CREATE TABLE CheckHasGuidAll (
48 dbname varchar(100),
49 tblname varchar(100),
50 hasguid varchar(100)
51);
52
53
54declare @db varchar(100)
55DECLARE @username VARCHAR(100)
56 declare @command varchar(2000)
57 declare @sqlcommand varchar(max)
58
59 PRINT '##### BEGIN DROP USER SCRIPT ##### '
60 ----------------------------
61 DECLARE Del_user_cursorDB CURSOR FOR
62 SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'
63 OPEN Del_user_cursorDB
64
65 FETCH NEXT FROM Del_user_cursorDB INTO @db
66
67 WHILE @@FETCH_STATUS = 0
68 BEGIN
69
70SET @sqlcommand = '
71 INSERT INTO CheckHasGuid (dbname, tblname, hasguid)
72(SELECT
73 '''+CONVERT(VARCHAR(100),@db)+''',
74 TableName = t.name,
75 ColumnName = col.name
76FROM
77 '+CONVERT(VARCHAR(100),@db)+'.sys.indexes ind
78INNER JOIN
79 '+CONVERT(VARCHAR(100),@db)+'.sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
80INNER JOIN
81 '+CONVERT(VARCHAR(100),@db)+'.sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
82INNER JOIN
83 '+CONVERT(VARCHAR(100),@db)+'.sys.tables t ON ind.object_id = t.object_id
84WHERE
85 t.name = ''attachments''
86 and t.type=''U''
87 --AND ind.name = ''guid_Attachments''
88 --AND ind.name like ''guid%''
89 -- and syst.name=''attachments'' and syst.type=''U''
90)'
91
92 --------------------------------
93 INSERT INTO CheckHasGuidAll (dbname)
94(SELECT name FROM sys.databases)
95
96DELETE CheckHasGuidAll FROM CheckHasGuidAll a
97INNER JOIN CheckHasGuid b
98ON a.dbname = b.dbname
99-------------------------
100
101exec SP_MSFOREACHDB @sqlcommand
102
103print @sqlcommand
104
105FETCH NEXT FROM Del_user_cursorDB INTO @db
106END
107
108 CLOSE Del_user_cursorDB
109 /*Deallocate the cursor:*/
110 DEALLOCATE Del_user_cursorDB
111
112 ------------------------------
113
114 INSERT INTO CheckHasGuidAll (dbname, tblname,hasguid)
115 (select distinct * from CheckHasGuid)
116
117 UPDATE CheckHasGuidAll
118 SET hasguid = null
119 WHERE hasguid NOT LIKE 'GUID'
120
121 insert into CheckHasGuidX (dbname, tblname, hasguid)
122 (select distinct dbname, tblname, hasguid from CheckHasGuidAll)
123
124 insert into CheckHasGuidY (dbname, tblname)
125 (select distinct dbname, tblname from CheckHasGuidX)
126
127 insert into CheckHasGuidA (dbname, tblname)
128 (select distinct dbname, tblname from CheckHasGuidX)
129 insert into CheckHasGuidB (dbname, hasguid)
130 (select distinct dbname, hasguid from CheckHasGuidX where hasguid = 'GUID')
131
132 update CheckHasGuidA
133 SET hasguid = CheckHasGuidB.hasguid
134 FROM CheckHasGuidA
135 JOIN CheckHasGuidB ON CheckHasGuidA.dbname = CheckHasGuidB.dbname
136 where CheckHasGuidA.dbname = CheckHasGuidA.dbname
137
138 select * from CheckHasGuidA
139
140 END