I want to find a sql command or something that can do this where I have a table named tblFoo and I want to name it tblFooBar. However, I want the primary key to also be change, for example, currently it is:
CONSTRAINT [PK_tblFoo] PRIMARY KEY CLUSTERED
And I want a name change to change it to:
CONSTRAINT [PK_tblFooBar] PRIMARY KEY CLUSTERED
Then, recursively go through and cascade this change on all tables that have a foreigh key relationship, eg. from this:
CHECK ADD CONSTRAINT [FK_tblContent_tblFoo] FOREIGN KEY([fooID])
To this:
CHECK ADD CONSTRAINT [FK_tblContent_tblFooBar] FOREIGN KEY([fooID])
Naturally, I am trying not to go through and do this all manually because a) it is an error prone process, and b)it doesn't scale.
No, rename of the table does not cause indexes to rebuild.
Using SQL Server Management StudioIn Object Explorer, right-click the table you want to rename and choose Design from the shortcut menu. From the View menu, choose Properties. In the field for the Name value in the Properties window, type a new name for the table.
This is just off the top of my head and isn't complete (you'd need to add similar code for indexes). Also, you would need to either add code to avoid renaming objects from a table with the same base name, but additional characters - for example, this code would also list tblFoo2 and all of its associated objects. Hopefully it's a start for you though.
DECLARE
@old_name VARCHAR(100),
@new_name VARCHAR(100)
SET @old_name = 'tblFoo'
SET @new_name = 'tblFooBar'
SELECT
'EXEC sp_rename ''' + name + ''', ''' + REPLACE(name, @old_name, @new_name) + ''''
FROM dbo.sysobjects
WHERE name LIKE '%' + @old_name + '%'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With