September 14, 2011 at 1:54 pm
hello, I would like to remove from the machine "view of the dead" (not having the source table). I do not know how to bite on. Help
September 14, 2011 at 2:01 pm
not too sure what you mean, are you saying you have a view called dead object?
***The first step is always the hardest *******
September 14, 2011 at 2:11 pm
I want to delete views that do not have the source table by the procedure or application. net
September 14, 2011 at 2:15 pm
views(as well as procs and functions) can become invalid if the underlying tables/views they use have changed, got dropped, etc.
in SQL 2000 (the forum you posted in) you cannot use Try..Catch to build a list, so you might need to do it the hard way.
EXEC sp_refreshview VIEWNAME for each view in your database...if it fails, it's invalid and needs to be fixed, otherwise the view is OK.
if you are in 2005 and above, i have a kewl script to genrate the list of invalid objects.
Lowell
September 15, 2011 at 9:48 am
HI if your willing to share i would love a copy of the script you mention i have SQL2005 and 2008
***The first step is always the hardest *******
September 15, 2011 at 9:55 am
glen.wass (9/15/2011)
HI if your willing to share i would love a copy of the script you mention i have SQL2005 and 2008
It's here on the forums in a few different posts; it's gotten a thumbs up or two from folks i respect here, so i guess it passed a bit of peer review here:
SET NOCOUNT ON
DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(max)
--#################################################################################################
--Views
--#################################################################################################
DECLARE acursor CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(v.name)
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
OPEN acursor
FETCH NEXT FROM acursor INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
exec sp_refreshview @objname
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM acursor INTO @objname
END
CLOSE acursor
DEALLOCATE acursor
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))
print @cmd
exec (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))
print @cmd
exec (@cmd)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
SELECT * FROM @BadObjects
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply