March 20, 2008 at 6:21 am
Hi All,
I have to update with new guid for all the uniqueidentifier columns for all the tables having uniqueidentifier as datatype in a database.
Any idea plz?
---
March 20, 2008 at 7:12 am
First off, if these are primary keys and you have referential integrity, you have a MAJOR task in front of you. Why would you want to do something like this?
Otherwise, I suggest a select statement to get the list of tables that have a column of that data type and then a cursor to walk through them doing updates using dynamic SQL. No other way really comes to mind.
This query will list the tables and columns you need to address:
SELECT TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'uniqueidentifier'
That'll get you started.
The update statement for the table is really easy:
UPDATE [HumanResources].[Employee]
SET [HumanResources].[Employee].[rowguid] = NEWID()
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2008 at 7:47 am
Hi Grant Fritchey,
Thanks for quick reply.
We have to change all the names as well as guid for some purpose.For that we need this.
Your solution will give some relax for me. But i need to do the same for all the table columns having uniqueidentifier.
Any way again tks for your reply. I will do my best for that. 🙂
---
March 20, 2008 at 8:03 am
sqluser (3/20/2008)
But i need to do the same for all the table columns having uniqueidentifier.---
The first query will show you all tables and all columns with uniqueidentifiers. You'll then need to put that into a cursor and simply build an dynamic query to execute the update. That's all. It should do exactly what you're asking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2008 at 12:02 am
Hi Grant Fritchey,
I tried with cursor. Like this.
DECLARE @TableName varchar(256),
@ColumnName varchar(256)
DECLARE Cursor_GUID CURSOR FOR
SELECT TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'uniqueidentifier'
OPEN Cursor_GUID
FETCH NEXT FROM Cursor_GUID INTO @TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName
PRINT @ColumnName
UPDATE @TableName
SET @ColumnName = NEWID()
FETCH NEXT FROM Cursor_GUID
INTO @TableName,@ColumnName
END
CLOSE Cursor_GUID
DEALLOCATE Cursor_GUID
But i am getting error while update.
Must declare @TableName.
Here my question is, is it possible to give variablename in the update statement?
---
March 24, 2008 at 4:13 am
No, you can't pass the table name as a variable. You're close though. Instead of this:
UPDATE @TableName
SET @ColumnName = NEWID()
Create a variable of type nvarchar(max) and try this:
SET @sql = 'UPDATE ' + @TableName + 'SET ' + @ColumnName + ' = NEWID()'
SP_EXECUTESQL @sql
That ought to work for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply