Repair VIEW_DEFINITION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Repair VIEW_DEFINITION

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-11-28 : 08:43:03

Hello

If i do a

select * from information_schema.views where VIEW_DEFINITION not like '%'+TABLE_NAME+'%'

I have severall view_definition not matching the 'TABLE_NAME'

This can cause trouble if I use the View_Definition syntax to script view-creation

How can I fix tahat ?
Thank for any help

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 08:49:16

I am not sure I follow. The Table_name in information_schema is the view name, and the view_definition is the script to create it. Are you saying the table name in the view definition is not the same as the table name in the Create View syntax?

for example you have a table_name = 'vwTest' but the view definition has 'Create View vtTest1 as...'?

This would only happen if the views were renamed via SMSS or using sp_rename...(and subsequently not dropped old/recreated with the new name)

you can use sp_rename [correctname],[incorrectname] to correct them.

Poor planning on your part does not constitute an emergency on my part.

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-11-28 : 09:08:27

Thank you dataguru 1971

quote:
for example you have a table_name = 'vwTest' but the view definition has 'Create View vtTest1 as...'?

This would only happen if the views were renamed...(and not dropped old/recreated with the new name)

This is the problem indeed
Is there any way to fix that ?

I've tryed

exec sp_refreshview MyView

But with no effect

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 09:19:34

you can use sp_rename [correctname],[incorrectname] to correct them.

If there are that many, use SMSS to generate script to drop and create all views (the names will be correct) and execute the script.

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 09:34:42

This hack job will do the sp_rename. I tested on a couple quick views I made and renamed using SMSS and did the trick..probably not ideal.

I suggest printing the @SQL before letting it execute to be sure (the exec statement is commented out below)


Declare @SQL varchar(1000)
Declare @wrong varchar(25), @right varchar(25) 
Set @SQL = ''

Declare cView CURSOR   
FOR
Select Table_Name,
RTRIM(substring(VIEW_DEFINITION,13,charindex(' ',View_Definition,13)-charindex('AS',View_Definition,13)-3)) as CorrectName

FROM information_Schema.views
where view_definition not like '%'+table_name+'%'

OPEN cView
	FETCH NEXT FROM cView INTO @wrong,@right
	WHILE @@Fetch_status = 0
	BEGIN
		Select @SQL = 'sp_rename ' + char(39)+ @wrong + char(39) + ','+char(39) + RTRIM(@right) + char(39)
			--exec (@SQL)  --will execute the statement
 			Print @SQL --will print the executable
			Print @wrong + ' Corrected to: ' + @right
		 FETCH NEXT FROM cView INTO @wrong,@right
            END

Close cView
Deallocate cView


--check the results
Select *
FROM information_Schema.views
where view_definition not like '%'+table_name+'%'

Poor planning on your part does not constitute an emergency on my part.

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-11-28 : 10:12:41

Thank you for your hel

In fact the problem seem to be on the other side
TABLE_NAME gives the right name but VIEW_DEFINITION use a wrong name

For now I have manualy re-save the view from MS Designer

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 10:43:59

ahh..well that would be different. You would have to replacy @wrong with @right using a REPLACE function instead of sp_rename to do something like what I did. Best way would have been to script drop , and script create then execute the scripts. Manually renaming is what caused the problem in the first place..

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources