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+'%' ) beginRaiserror('One or more procedures depend on this table and cant be dropped' , 16, 1) Rollbackend GO create procedure test_spasselect * from test_table GO drop table test_table
Now you would get error as test_sp is referencing the table
Tags: t-sql, sql_server, procedure, integrity,