SQL 2005 Force Table Rename That Has Dependencies

sql 2005 force table rename that has dependencies

Find the "enforced dependencies", then remove or disable them.

By "enforced dependencies", it means Schema binding, so you'll have to look specifically for that.

Here's a query to look for schema binding references to your object:

select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = @YourObjectName

As I noted in the comments, there is no way to FORCE-ibly override Schema Binding. When you use Schema Binding, you are explicitly saying "Do not let me or anyone else override this." The only way around Schema Binding is to undo it, and that's intentional.

SQL Server 2005, need script to check for all constraints on table

To disable all contraints

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"
go

To Re-enable

sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER  all"
go

You should easily be able to adapt this to just one table.

How to rename a table in SQL Server?

To rename a table in SQL Server, use the sp_rename command:

exec sp_rename 'schema.old_table_name', 'new_table_name'

Indexed view in Sql Server 2005 Error

Just as the error says, you can't have an index on a view that isn't schema bound. To schemabind the view use

create view with schemabinding.

all tables referenced int the view must be fully quallified with the schemaname, i.e. dbo.table, not just table

Getting error while opening the database?

Take a look at the Microsoft Documentation....

Troubleshooting Server and Database Connection Problems

---

Related Topics