December 12, 2003 at 7:31 am
I need to fix a database which someone has inadvertently messed up. I cannot fix the one with the errors b/c it resides in production, so I need to either script it out or something so that I can fix the problems.
The problem is: someone has changed the names of views, stored procs, etc. through the EM GUI. They did not recompile them or fix the original internal scripts so they were updated, which in turn would have updated the syscomments table. When I script the database to a new server, it writes out the original view, sp.. names - not the updated ones. So in turn I am having problems finding everything that was updated (which I will need to have to update all the internal code and recompile them). Does anyone have any suggestions about how to go about this?
December 12, 2003 at 7:44 am
maybe this helps :
select o.name
, c.text
FROM dbo.sysobjects O
inner join dbo.syscomments C
on O.id = C.id
and O.xtype = 'V'
where C.text like '%CREATE VIEW %'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2003 at 8:00 am
Yes, tried this... I can through them all manually and look for the name differences, however, one other piece to the puzzle is that inside the views, etc. there are table names that still reference tables where the names have been changed. For example: 'table name' has been changed to 'tablename'. The view still works b/c the views have not been recompiled. But when I script out the database, it puts out all of the old names instead of the new ones and then the DTS packages, etc are not able to complete. Am I just going in circles, so tired of looking at it...
December 15, 2003 at 12:58 am
This is a long shot, but maybe you could download a trial copy of erwin(CA) or another dbcompare-tool, reverse engineer your scripted db and compare it to a reverse engineered copy of you db ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2003 at 10:39 pm
This code is not a complete solution but might do you some good. It takes the text definition of all views and strips out character strings and comments, then replaces tabs, CR/LF, and []." delimiters with blanks. Then it checks to see if the view name appears in the definition between the words VIEW and AS. It also checks that all referenced table names appear after the word FROM.
The parsing is pretty rudimentary, and could be fooled by object names with embedded blanks or by complex views with UNION queries or subqueries, but it's a start. It could be extended to check procedure names, but it's not clear how you could reliable check for renamed tables in procedure definitions.
declare @vn varchar(100), @TN varchar(100), @t varchar(8000)
declare @p1 smallint, @p2 smallint
declare viewlist cursor local fast_forward for
select rtrim(table_name) as table_name, view_definition from information_schema.views
declare @tbls cursor
open viewlist
fetch next from viewlist into @vn, @t
while @@fetch_status=0 begin
set @p1 = charindex('''',@t)
while @p1 > 0 begin
set @p2 = charindex('''',@t, @p1+1)
if @p2 > 0 set @t = substring(@t,1,@p1-1) + ' ' + substring(@t,@p2+1,8000)
set @p1 = charindex('''',@t, @p1+1)
end
set @p1 = charindex('/*',@t)
while @p1 > 0 begin
set @p2 = charindex('*/',@t, @p1+2)
if @p2 > 0 set @t = substring(@t,1,@p1-1) + ' ' + substring(@t,@p2+2,8000)
set @p1 = charindex('/*',@t, @p1+1)
end
set @p1 = charindex('--',@t)
while @p1 > 0 begin
set @p2 = charindex(char(13),@t, @p1+1)
if @p2 > 0 set @t = substring(@t,1,@p1-1) + ' ' + substring(@t,@p2+1,8000)
set @p1 = charindex('--',@t, @p1+1)
end
set @t = replace(replace(replace(@t,char(9),' '),char(13),' '),char(10),' ')
set @t = replace(replace(replace(replace(@t,'[',' '),']',' '),'.',' '),'"',' ')
if not charindex(' ' + @vn + ' ', @t) between charindex(' VIEW ', @t) AND charindex(' AS ',@t)
begin
set @p1 = charindex(' VIEW ', @t) + 6
set @p2 = charindex(' AS ',@t)
print 'View ' + rtrim(substring(@t, @p1, @p2 - @p1)) + ' was renamed ' + @vn
end
set @p1 = charindex(' FROM ', @t) + 5
set @tbls = cursor local fast_forward for select rtrim(table_name)
from information_schema.view_table_usage where view_name = @vn
open @tbls
fetch next from @tbls into @TN
while @@fetch_status = 0 begin
if charindex(' ' + @TN + ' ', @t, @p1) = 0
print 'Table name ' + @TN + ' not found in view definition for ' + @vn
fetch next from @tbls into @TN
end
close @tbls
fetch next from viewlist into @vn, @t
end
close viewlist
December 16, 2003 at 6:50 am
Thank you! I will give it a shot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply