problem dropping columns

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, DougYou can find information about dependencies of some
particular column from system tables:
sysconstraints,
syscolumns,
sysobjects
Take a look about this tables in BOL.
Regards
----
All information provided above AS IS
>--Original Message--
>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
>
>.
>