Convert Unique Identifier to Varchar data type

,

Script will drop the Default, Primary, Unique, Foreign Constraints, Index and ROWGUID on Unique Identifier data type column and finally it alters the unique identifier to varchar (38) data type. I have compile and execute the script in my database server. Script has executed successfully.

SET QUOTED_IDENTIFIER OFF

DECLARE @ObjectID Int, @ObjectName Varchar(100),
	@EmptyString Char(1), @IsDefaultBound Char(1),
	@ExecuteScript Varchar(8000), @IndexName Varchar(256),
	@Loop Int

set nocount on

Create Table #TableInformation (ObjectID Int Not Null, ObjectName Varchar(100) Not Null, 
				Parent_ObjectID Int Not Null, Parent_ObjectName Varchar(100) Not Null,
				ObjectType Char(2) Not Null)
--				IsRowGuid Char(1) Not Null, IsDefaultBound Char(1) Null)
DECLARE csrTableName CURSOR FOR 
SELECT SO.ID, SO.Name
FROM SysObjects SO
WHERE SO.xType = 'U' 
ORDER BY SO.Name

Set @EmptyString = ''

OPEN csrTableName

FETCH NEXT FROM csrTableName 
INTO @ObjectID, @ObjectName

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN

-- 36 = Unique Identifier data type

--Generate Script to Drop Foreign Constraint & Select statement will generate Foreign Key ID, Foreign Key Name, 
--Parent Object ID & Name for Foreign Key and Object Type As "FK"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
	From SysForeignKeys Where rkeyID = @ObjectID
	Union
	Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
	From SysForeignKeys Where fkeyID = @ObjectID

--Generate Script to Drop Primary and Unique Constraint & Select statement will generate  Primary Key ID, 
--Primary Key Name, Parent Object ID & Name for Primary Key and Object Type As "PK"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select ID, Object_Name(ID), @ObjectID, @ObjectName, 'C'
	From SysObjects Where Parent_Obj = @ObjectID And xType In ('PK', 'UQ') 

--Generate Script to Default Constraint & Select statement will generate Parent Object ID, Default Column Name & 
--Parent Object ID & Name for Default Column and Object Type As "D"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select cDefault, Object_Name(SC.cDefault), @ObjectID, @ObjectName, 'C' 
	From SysColumns SC, SysComments SCM
	Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsDefaultCnst') = 1 And
	SC.xType = 36 

--Generate Script to Drop ROWGUID & Select statement will generate RowGUID Column ID, RowGUID Column Name & 
--Parent Object ID & Name for RowGUID Column and Object Type As "RG"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select ID, Name, @ObjectID, @ObjectName, 'RG'
	From SysColumns Where ID = @ObjectID And Number = 0 And ColumnProperty(@ObjectID, Name, 'IsRowGuidCol') = 1

--Generate Script to Alter Column to Varchar(38) & Select statement will generate UniqueIdentifier Type ID, 
--UniqueIdentifier Column Name, Parent Object ID & Name for UniqueIdentifier Column and Object Type As "UI"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select xType, Name, ID, Object_Name(ID), 'UI' 
	From SysColumns Where ID = @ObjectID And xType = 36
	
--Generate Script to UnBind Defaults for ID Column & Select statement will generate Parent Object ID, 
--Default Column Name & Parent Object ID & Name for Default Column and Object Type As "DF"
	Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
	Select @ObjectID, SC.Name, @ObjectID, @ObjectName, 'DF'
	From SysColumns SC, SysComments SCM
	Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsConstraint') = 0 And 
	SC.xType = 36

		Declare csrIndexName CURSOR FOR 
		Select Name From SysIndexes 
		Where ID = @ObjectID

		OPEN csrIndexName
		
		FETCH NEXT FROM csrIndexName 
		INTO @IndexName
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			Set @Loop = 0
			While @Loop < 10
				Begin
					IF Exists (Select SC.Name, SC.ID From SysColumns SC, SysIndexes SI
						   Where SC.ID = SI.ID And SI.ID = @ObjectID And 
							SI.Name = @IndexName And SC.xtype = 36 And SI.Name Not Like '%_WA_%' And
							SC.Name = Index_col(Object_Name(@ObjectID), SI.indid,@Loop))
					Begin
--Generate Script to drop Index & Select statement will generate Parent Object ID of the Index Column, 
--IndexName, Parent Object ID & Name of the Table Name, and Object Type As "I"
						Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType) 
						Select @ObjectID, @IndexName, @ObjectID, @ObjectName, 'I'
						Break
					End				
					Set @Loop = @Loop + 1
				End
				
			FETCH NEXT FROM csrIndexName 
			INTO @IndexName
		END
		
		CLOSE csrIndexName
		DEALLOCATE csrIndexName
		
	FETCH NEXT FROM csrTableName 
	INTO @ObjectID, @ObjectName
END

CLOSE csrTableName
DEALLOCATE csrTableName

PRINT @EmptyString
	
	Select * From #TableInformation Order By ObjectType
	
	PRINT @EmptyString
	 
	DECLARE csrScript CURSOR FOR 
	

	Select 	Case 	When ObjectType = 'DF' Then 
			'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Name = "' + ObjectName + '" And cDefault > 0 )' + Char(13) + 
			'Exec sp_unbindefault "[' + Parent_ObjectName + '].' + ObjectName + '"' + Char(13) --+ 'GO' 

			When ObjectType = 'RG' Then 
			'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Number = 0 And
			Name = "' + ObjectName + '" And ColumnProperty(Object_ID ("[' + Parent_ObjectName + ']")' + ', Name, "IsRowGuidCol") = 1 )' + Char(13) + 
			'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] DROP ROWGUIDCOL' + Char(13) --+ 'GO' 
			
			When ObjectType = 'C' Then 
			'If Exists (Select ID From SysObjects Where ID = Object_ID("[' + ObjectName + ']") And Parent_Obj = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) + 
			'ALTER TABLE [' + Parent_ObjectName + '] DROP CONSTRAINT [' + ObjectName + ']' + Char(13) --+ 'GO'
			
			When ObjectType = 'I' Then 
			'If Exists (Select ID From SysIndexes Where Name = "' + ObjectName + '" And ID = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) + 
			'DROP INDEX [' + Parent_ObjectName + '].' + ObjectName + + Char(13) --+ 'GO'

			When ObjectType = 'UI' Then 
			'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] Varchar(38)' + Char(13) --+ 'GO' 

		End As "Script"
	From #TableInformation Order By ObjectType
	
	Set @ExecuteScript = ''
	
	OPEN csrScript
	
	FETCH NEXT FROM csrScript 
	INTO @ExecuteScript
	
	PRINT @EmptyString
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Print (@ExecuteScript)
		Exec (@ExecuteScript)
		FETCH NEXT FROM csrScript 
		INTO @ExecuteScript
	END
	
	CLOSE csrScript
	DEALLOCATE csrScript

Drop Table #TableInformation

set nocount off

GO

Rate

Share

Share

Rate