Articles
Working with SQL Server schemas: the changes that are harder than they look, and what to do
about them.
- How to change a primary key that foreign keys already point at
SQL Server will not drop a primary key while a foreign key references it. The order of work, and how to put back every key you had to drop.
- Alternatives to SSMS database diagrams, and when you still want them
SSMS diagrams are free and built in. They also write objects into your database, need db_owner, and apply edits to the live schema on save.
- ALTER COLUMN failed because one or more objects access this column
Msg 4922 names no culprit. Here is how to list everything holding the column, in what order to clear it, and the change that needs none of it.
- CREATE UNIQUE INDEX terminated because a duplicate key was found
Msg 1505 names one duplicate value, often NULL. Here is how to list them all, why two nulls collide, and the index that allows them.
- Renaming a column in SQL Server, and what it quietly breaks
sp_rename updates indexes and foreign keys for you, and leaves every view and procedure naming a column that no longer exists.
- The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
Msg 547 names the parent table, but the rows at fault are in the child. Here is how to find them, and what WITH NOCHECK really costs.
- A SQL Server view built on SELECT * can return the wrong columns
Add or remove a column and a SELECT * view keeps its old column list, sometimes showing data under the wrong name. Why, and how to fix it.
- Changing a column from NULL to NOT NULL in SQL Server
ALTER COLUMN … NOT NULL fails while any row holds NULL. Find the empty rows, fill them, then tighten the column, including when its type changes too.
- How to add a NOT NULL column to a SQL Server table that already has rows
ALTER TABLE ADD fails on a table with data unless something supplies a value. Three ways to do it, and how to end up with no default if you want none.
- How to drop a table that is referenced by a FOREIGN KEY constraint
SQL Server will not drop a table other tables point at, and has no CASCADE. How to find every foreign key involved, and drop in the right order.
- How to find every view, procedure and function that uses a table in SQL Server
Before you change a table, find what reads it. What sys.sql_expression_dependencies tells you, and the places it cannot see.
- Why DROP COLUMN fails: the object 'DF__…' is dependent on column
A default is a separate object, and SQL Server will not drop a column while one is attached. How to find its name and drop both safely.
- You cannot add IDENTITY to an existing column in SQL Server
ALTER COLUMN cannot add, remove or retarget an identity. Here are the three ways round it, and the one that quietly does nothing.
Also here