How can I set a GUID column to ROWGUID using T-SQL?

I have a source table with a primary key column that is a guid and is set as rowguid when the table was created.  I want to duplicate this table using a select-into command and then perform some actions on it and then drop the original table and rename this clone to the name of the original and continue.  I can use T-SQL to add the indexes and defaults back to the table but I can't figure out the syntax to set the column back to ROWGUID.  Does anyone know how to do it?
YellowbusTeamAsked:
Who is Participating?
 
ChrisedeboConnect With a Mentor Commented:
This should do what you're after :o)
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tablename ALTER COLUMN columnname
	ADD ROWGUIDCOL
GO
COMMIT

Open in new window

0
 
YellowbusTeamAuthor Commented:
That's perfect, thanks Chrisedebo :)
0
 
chapmandewCommented:
like this:

ALTER TABLE TableName ALTER COLUMN guidfield
      ADD ROWGUIDCOL
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ChrisedeboCommented:
If you need to know the syntax for something you know how to do in sql management studio, make the change you want to then click on the generate change script button (looks like a little disk with a script behind it) on the tool bar and it will generate something similar to the script above.......not that I'm going to admit I'm a dirty cheat ;o)
0
 
chapmandewCommented:
I'm the same way.  :)  I added the rowguid in management studio and watched profiler to see what the interface did.
0
 
YellowbusTeamAuthor Commented:
Aha - good tip -  I knew about that for creating things but didn't know you could find change syntax with it :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.