Madhivanan

Forcing integrity between tables and procedures

You can force integerity between tables and views with schemabinding when you create views so that tables cant be dropped until views that bind on tables are dropped. Unfortunately it is not possible to force this integrity between tables and procedures. Tables can be dropped though there are procedures referencing them

But if you use SQL Server 2005 or higher version, it is possible with DDL trigger which is one of finest features available

CREATE TABLE test_table(code int, names varchar(100))

GO
CREATE TRIGGER alert_me
ON DATABASE 
FOR DROP_TABLE 

AS
declare @table_name varchar(100)
select @table_name= right(ddl,charindex(' ',reverse(ddl))-1) from
(
select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)') as DDL
) as t 


If exists
(
select * from information_schema.routines
where routine_definition like '% '+@table_name+'%'
)

begin
Raiserror('One or more procedures depend on this table and cant be dropped' , 16, 1) 
Rollback
end

 
GO
 
create procedure test_sp
as
select * from test_table

GO
drop table test_table



Now you would get error as test_sp is referencing the table