Here is some code to get a list of identity columns in database.
select so.name as TableName, + o.list as IdentityColumnName
from sysobjects so
case when exists (
select id from syscolumns
and columnproperty(id,name,'IsIdentity') = 1
end + ' '
from information_schema.columns where table_name = so.name
) o (list)
where xtype = 'U'
AND o.list is not null
AND name NOT IN ('dtproperties')
Here is a script to get a list of columns for each table.
SELECT t.name AS TABLE_NAME, c.name AS COLUMN_NAME, c.column_id AS COLUMN_ORDER, c.system_type_id AS DATA_TYPE, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
FROM sys.columns AS c
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes.
You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.”
What a pesky problem. It prevents you from making progress when you making database changes. It’s a good thing you can turn it off.
This is caused when you make one of the following changes:
- You change the Allow Nulls setting for a column.
- You reorder columns in the table.
- You change the column data type.
- You add a new column.
Here’s how you fix it.
In Management Studio, go to Tools –> Options –> Designers –> Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option.
Now Management studio will work like expected.
Microsoft recommends you don’t turn this option off and that you use T-SQL to make changes to your tables.
Turning off this option will conflict if you have Change Tracking feature turned on. If you turn off this option and make a change to table with change tracking on all the tracking changes for that table will also be deleted.
To check if you have change tracking turned on, right click on your table and go to properties, look for the option Change Tracking.
Here is the kb article from microsoft: http://support.microsoft.com/default.aspx/kb/956176