Renaming all default contrainsts

[katehol]

I am currently trying to rename all of the Default constraints in my database to fit in with my naming convention of DF_+TableName+ColumnName. Since there are over 300 of the things, I don't particularly fancy doing them all manually.

I was hoping to use something like...

--Declare the variables
DECLARE @defname VARCHAR(100), @tblname VARCHAR(100), @colname VARCHAR(100), @cmd VARCHAR(1000)

--Set the table name
SET @tblname = (SELECT object_name(parent_obj) FROM sysobjects
                LEFT JOIN sys.columns ON sys.columns.object_id = sysobjects.id
                INNER JOIN sysconstraints ON sysobjects.id = sysconstraints.constid
                WHERE sysobjects.xtype = 'D')

--Set the column name
SET @colname = (SELECT name FROM sys.columns
                INNER JOIN sys.tables ON sys.tables.object_id = sys.columns.object_id
                WHERE sys.tables.name = @tblname)

--Set the default constraint
SET @defname = (SELECT name FROM sysobjects JOIN sysconstraints
                ON sysobjects.id = sysconstraints.constid
                WHERE object_name(sysobjects.parent_obj) = @tblname
                AND sysobjects.xtype = 'D'
                AND sysconstraints.colid = (SELECT colid FROM syscolumns
                                             WHERE id = object_id(@tblname) AND
                                             name = @colname))

 --Set the renaming
SET @cmd = EXEC sp_rename +@defname+, 'DF_'+@tblname+'_'+@colname+, 'OBJECT'

--Execute the renaming
EXEC(@cmd)

I know that the above is not correct in any way, but it is how far I have got before I got really stuck! And I thought it might help you see what I was trying to get at...

Any clues?

 

[RiverGuy]

The correct way IMO would be to build a script which you can verify before executing. So, let SQL Server do the work for you. Run this is Management Studio, and paste the results back into Management Studio. Tweak as needed and execute when satisfied with the results.

SELECT 'EXEC sp_rename ''' + ConstraintName + ''', ''DF_' + Parent + ColumnName + ''', ''OBJECT'''
FROM
	(SELECT object_name(a.parent_obj) AS Parent, c.name AS ColumnName, a.name AS ConstraintName
	FROM sysobjects a
	INNER JOIN sysconstraints b ON a.id = b.constid
	INNER JOIN syscolumns c ON b.colid = c.colid AND c.id = a.parent_obj
	WHERE a.xtype = 'D') d

 

[katehol]

Thanks for that - not quite as pretty, but definitely functional!
Kate )

 

[ESquared]

master.dbo.sp_execresultset

but like he said, you should probably vet the list before using it to really alter names, at least the first time.

 

[katehol]

When I try to run the script with sp_execresultset in front of it, I get an error saying it can't find sp_execresultset - is this undocumented stored proc available in SQL Server 2005?

 

[ESquared]

Might be SQL 2000 only.

But you could write your own version of it if you need it so badly, but of course it's going to be dynamic SQL.

 

[katehol]

This undocumented stored procedure is no longer available in SQL 2005.

I actually found the script on the internet though -

http://network.programming-in.net/articles/SQL.asp?SQL=sp_execresultset