Thursday, April 30, 2015

validate procedures in a database


its possible that the database may have some procedures which has missing dependent objects.
mostly it happens when the dependent objects got deleted or modified.

to get the list of the objects which are not valid. run the procedure below.

Test : Create a procedure on a table which doesnt exist

create proc pr_invalid_object
as
select * from no_table_154687321365746321654657


run the below procedure. you will get the output of the above procedure


alter PROC pr_validate_db_objects
AS

SET NOCOUNT ON 
CREATE TABLE #InvalidObjects (objname VARCHAR(500))

CREATE TABLE #t1 (objname VARCHAR(500))

DECLARE @name VARCHAR(256)

DECLARE cur CURSOR
FOR
SELECT isnull(schema_name(schema_id), 'dbo') + '.' + NAME
FROM sys.objects
WHERE type IN (
'P'
,'FN'
)

OPEN cur

FETCH NEXT
FROM cur
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF (
SELECT COUNT(*)
FROM sys.dm_sql_referenced_entities(@name, 'OBJECT') b
) > - 1
BEGIN


INSERT INTO #t1
SELECT @name
END
END TRY

BEGIN CATCH
INSERT INTO #InvalidObjects
SELECT @name
END CATCH

FETCH NEXT
FROM cur
INTO @name

CONTINUE
END

CLOSE cur

DEALLOCATE cur

SELECT A.objname ,B.modify_date AS created_date FROM #InvalidObjects  A JOIN (SELECT isnull(schema_name(schema_id), 'dbo') + '.' + name  AS objname ,modify_date FROM sys.objects)B ON A.objname  = B.objname 
order by 2 desc 



Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors