sql server clients: problem dropping columns -- sql server clients

problem dropping columns

MSDN
10/24/2003 11:49:07 AM

sql server clients: One of our developers accidentally added a 'rowguid' column to all of our
tables (mssql 2000). I'm trying to write a script that will drop this
column from all the tables; however, I've run into a problem where I can't
drop them because there are dependant contraints/indexes. The following
code is what I have so far. Is there's a way to identify and drop all
dependancies on this column first?

DECLARE @TableName sysname
DECLARE @ColumnName sysname

DECLARE RowGuidColumnList CURSOR
FOR select t.name, c.name

FROM sysobjects t
JOIN syscolumns c
ON (c.id = t.id and t.type = 'U')
WHERE c.name = 'rowguid'
order by t.name, c.name

OPEN RowGuidColumnList

FETCH NEXT FROM RowGuidColumnList
INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Removing rowguid column from ' + @TableName

execute('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName)

FETCH NEXT FROM RowGuidColumnList
INTO @TableName, @ColumnName

END

CLOSE RowGuidColumnList
DEALLOCATE RowGuidColumnList

Thanks, Doug

Re: problem dropping columns

John Bell
10/27/2003 11:58:07 AM

Hi

You may want to check if an entry exists in the view
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for the given column.

John

[quoted text, click to view]