locked
DataType Change in Databases to support multilanguage RRS feed

  • Question

  • i need to change the production database structures with multi language Support , datatype from VARCHAR to NVARCHAR in all SQL Objects

    SQL Objects: Tables, Functions, Stored Procedures

    Some of production Tables with 15 Croces records with 10 indexes

     could anyone suggest me, how to change the entire databases through SQL scripting


    Sunday, September 13, 2015 9:32 AM

Answers

  • I would use Aaron's stored procedure to look for VARCHAR as a string ( to be replaced with NVARCHAR)

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 13, 2015 9:38 AM
  • Finally i have written a stored procedure to change nvarchar datatype whereever char and varchar datatype defined columns by passing the table name as input.

    Steps followed for nvarchar changes

    1. drop the indexes and constriants

    2 .alter the table structures with nvarchar

    3. recreate the indexes & Constraints


    Correct me, Is there any approach to get a solution

    CREATE PROCEDURE [dbo].[Mig_ObjectWise_DataTypeChange]
    @p_TableName			NVARCHAR(200) =''
    AS
    BEGIN
    
     
    SET NOCOUNT ON
    
    	DECLARE		 @l_i						INT
    				, @l_max					INT
    				, @l_tablename				NVARCHAR(200)
    				, @l_columnname				NVARCHAR(50)
    				, @l_datatype				NVARCHAR(50)
    				, @l_DTNULL					NVARCHAR(10)
    				, @l_indexname				NVARCHAR(200)
    				, @l_isindex				TINYINT					
    				, @l_sqlcreate				NVARCHAR(4000)
    				, @l_sqldrop				NVARCHAR(4000)
    				, @l_sql					NVARCHAR(MAX)
    				, @l_DBName					NVARCHAR(200)
    		
    		
    		IF OBJECT_ID('Tempdb..#Unicode_AlterTableStructure') IS NOT NULL
    			DROP TABLE #Unicode_AlterTableStructure
    				
    		CREATE TABLE #Unicode_AlterTableStructure
    				( 	
    					ID							INT					
    					, TableName					NVARCHAR(500)
    					, ColumnName				NVARCHAR(MAX)
    					, ColumnType				NVARCHAR(MAX)
    					, length					SMALLINT
    					, isnullable				INT
    					, ExistingColumnType		NVARCHAR(500)
    					, NewColumnType				NVARCHAR(500)
    					, Foreignkey				INT					
    					, OBJECTID					INT
    					, colid						SMALLINT
    				)
    		
    		IF OBJECT_ID('tempdb..#Unicode_AlterIndexConstraints') IS NOT NULL
    			DROP TABLE #Unicode_AlterIndexConstraints
    					
    		CREATE TABLE #Unicode_AlterIndexConstraints
    				(	
    					ID							BIGINT 
    					, RID						BIGINT
    					, DBName					NVARCHAR(200)
    					, TableName					NVARCHAR(500)
    					, IndexName					NVARCHAR(MAX)
    					, Index_Create_Statement	NVARCHAR(MAX)
    					, Index_Drop_Statement		NVARCHAR(MAX)
    					, OrderIndex				TINYINT
    					, OrderIndex1				TINYINT
    					, IS_Index					INT
    					, NewIndexConstraint		NVARCHAR(MAX)				
    				)	
    		
    		
    	BEGIN TRY
    	
    		BEGIN TRAN	
    			
    				SET @l_DBName = DB_NAME()
    		
    	--Step : 1 Impact Column for the given tables	
    	
    	 IF EXISTS ( SELECT TOP 1 1 FROM sys.foreign_key_columns 
    						WHERE parent_object_id = OBJECT_ID(@p_TableName)  OR referenced_object_id = OBJECT_ID(@p_TableName)  )		
    		
    		BEGIN
    			
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )		
    						
    			SELECT 	row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID))ID, *
    			FROM		(
    					SELECT	DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'
    														  ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType
    									, CASE WHEN fk.referenced_object_id IS NOT NULL THEN 0 ELSE 1 END AS Foreignkey							
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    					FROM	SYSCOLUMNS					SC  (NOLOCK)
    					JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    					JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id	
    					LEFT JOIN sys.foreign_key_columns	FK	(NOLOCK) ON FK.referenced_object_id		= tb.object_id	 
    					WHERE	ST.NAME IN ('CHAR','VARCHAR')						
    							and SC.id IN (	SELECT	DISTINCT referenced_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.referenced_column_id = SC.colid and FC.referenced_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	referenced_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	parent_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.constraint_column_id = SC.colid  and FC.parent_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	referenced_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	referenced_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.referenced_column_id = SC.colid  and FC.referenced_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	parent_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	parent_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.constraint_column_id = SC.colid and FC.parent_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	parent_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR'))	) A
    													
    			IF NOT EXISTS ( SELECT 1 FROM #Unicode_AlterTableStructure )										
    				
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )										
    			SELECT 	 row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID) )ID, *
    			FROM	(
    				
    						SELECT		DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'														  		
    														 ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType	
    									, 0	Foreignkey				
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    						FROM	SYSCOLUMNS					SC  (NOLOCK)
    						JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    						JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id		
    						WHERE	ST.NAME IN ('CHAR','VARCHAR') 						
    								and SC.id = OBJECT_ID(@p_TableName) 
    				) A
    													
    		END	
    		ELSE 
    			
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )										
    			SELECT 	 row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID) )ID, *
    			FROM	(
    				
    						SELECT		DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'
    														  
    														  ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType	
    									, 0	Foreignkey				
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    						FROM	SYSCOLUMNS					SC  (NOLOCK)
    						JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    						JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id		
    						WHERE	ST.NAME IN ('CHAR','VARCHAR') 						
    								and SC.id = OBJECT_ID(@p_TableName) 
    				) A
    				
    					
    	--Step 2 : Taking the index & constraints for the given table
    		
    		INSERT INTO #Unicode_AlterIndexConstraints
    				(ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint )		
    	
    		SELECT		ROW_NUMBER() OVER(ORDER BY OrderIndex)ID			-- remove the index & constraints based on ID
    					, ROW_NUMBER() OVER(ORDER BY OrderIndex1 )RID		-- recreate the index & constraints based on RID
    					, *		
    		FROM		(
    		
    				SELECT		@l_DBName DBName
    							, OBJECT_NAME(A.[object_id]) AS TableName
    							, A.[Name] As IndexName
    							, CAST(
    									Case	When A.type = 1 AND is_unique = 1 Then 'CREATE UNIQUE CLUSTERED INDEX ' 
    											When A.type = 1 AND is_unique = 0 Then 'CREATE CLUSTERED INDEX ' 
    											When A.type = 2 AND is_unique = 1 Then 'CREATE UNIQUE NONCLUSTEREd INDEX '
    											When A.type = 2 AND is_unique = 0 Then 'CREATE NONCLUSTERED INDEX '
    									End
    									--+ quotename(A.[Name]) 
    									+quotename(CASE WHEN A.[Name] LIKE '%__%' AND is_primary_key = 1 THEN 
    													'PK_'+OBJECT_NAME(A.[object_id])+'_'+
    														Stuff((		Select
    																			'_' + COL_NAME(A.[object_id],C.column_id)
    																			--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    																	From	sys.index_columns C WITH (NOLOCK)																															
    																	Where	A.[Object_ID] = C.object_id
    																			And A.Index_ID = C.Index_ID
    																			And C.is_included_column = 0
    																	Order by C.key_Ordinal Asc
    																	For XML Path('') ),1,1,'')
    													 WHEN A.[Name] LIKE '%__%' AND is_unique_constraint = 1 THEN 
    													 'UK_'+OBJECT_NAME(A.[object_id])+'_'+
    															Stuff((		Select
    																				'_' + COL_NAME(A.[object_id],C.column_id)
    																				--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    																		From	sys.index_columns C WITH (NOLOCK)																																				
    																		Where	A.[Object_ID] = C.object_id
    																				And A.Index_ID = C.Index_ID
    																				And C.is_included_column = 0
    																		Order by C.key_Ordinal Asc
    																		For XML Path('') ) ,1,1,'')
    														ELSE A.[Name] END)
    									
    									
    									+ ' ON ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ' ('
    									+ Stuff(
    											(
    												Select
    														',[' + COL_NAME(A.[object_id],C.column_id)+']'
    														--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    												From	sys.index_columns C WITH (NOLOCK)												
    												Where	A.[Object_ID] = C.object_id
    														And A.Index_ID = C.Index_ID
    														And C.is_included_column = 0
    												Order by C.key_Ordinal Asc
    												For XML Path('')
    											)
    											,1,1,'') + ') '
    									+ CASE WHEN A.type = 1 THEN ''
    											ELSE Coalesce('INCLUDE ('
    													+ Stuff(
    															(
    																Select
    																		',' + QuoteName(COL_NAME(A.[object_id],C.column_id))
    																From	sys.index_columns C WITH (NOLOCK)
    																Where	A.[Object_ID] = C.object_id
    																		And A.Index_ID = C.Index_ID
    																		And C.is_included_column = 1
    																Order by C.index_column_id Asc
    																For XML Path('')
    															)
    															,1,1,'') + ') '
    										,'') End As nvarchar(Max)) As Index_Create_Statement,
    							CASE WHEN is_primary_key = 1 THEN 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(A.[object_id])) + ' DROP CONSTRAINT ' +quotename(A.[Name])
    								WHEN is_unique_constraint = 1 THEN 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(A.[object_id])) + ' DROP CONSTRAINT ' +quotename(A.[Name])
    								ELSE 'DROP INDEX ' + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' 
    										+ quotename(OBJECT_NAME(A.[object_id])) + ';'END AS Index_Drop_Statement,
    							CASE WHEN ( is_primary_key = 1 or is_unique = 1 ) THEN 2 ELSE 3 END OrderIndex 
    							, CASE WHEN ( is_primary_key = 1 or is_unique = 1 ) THEN 0 ELSE 3 END OrderIndex1 
    							, 1 AS IS_Index
    							,CASE WHEN A.[Name] LIKE '%__%' AND is_primary_key = 1 THEN 
    									'PK_'+OBJECT_NAME(A.[object_id])+'_'+
    										Stuff((		Select
    															'_' + COL_NAME(A.[object_id],C.column_id)
    															--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    													From	sys.index_columns C WITH (NOLOCK)													
    													Where	A.[Object_ID] = C.object_id
    															And A.Index_ID = C.Index_ID
    															And C.is_included_column = 0
    													Order by C.key_Ordinal Asc
    													For XML Path('') ),1,1,'')
    								 WHEN A.[Name] LIKE '%__%' AND is_unique_constraint = 1 THEN 
    								 'UK_'+OBJECT_NAME(A.[object_id])+'_'+
    										Stuff((		Select
    															'_' + COL_NAME(A.[object_id],C.column_id)
    															--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    													From	sys.index_columns C WITH (NOLOCK)													
    													Where	A.[Object_ID] = C.object_id
    															And A.Index_ID = C.Index_ID
    															And C.is_included_column = 0
    													Order by C.key_Ordinal Asc
    													For XML Path('') ) ,1,1,'')
    								ELSE A.[Name] END AS NewIndexConstraint
    				From		SYS.Indexes A WITH (NOLOCK)
    				INNER JOIN	sys.objects B WITH (NOLOCK)	ON	A.object_id = B.object_id
    				INNER JOIN 	SYS.schemas S (NOLOCK)	ON	B.schema_id = S.schema_id            			     
    				Where		A.type IN (1,2) --clustered and nonclustered
    							AND B.Type != 'S'	 				
    							AND EXISTS (		SELECT	1
    												From	sys.index_columns	C WITH (NOLOCK)												
    												JOIN	#Unicode_AlterTableStructure UCC WITH (NOLOCK) 
    																			ON Object_id(ucc.TableName) = c.object_id and ucc.colid = c.column_id 
    																			AND c.index_id = a.index_id  AND A.object_id = Object_id(ucc.TableName))
    				
    				UNION ALL
    
    				SELECT	@l_DBName DBName
    						, OBJECT_NAME(b.parent_obj) AS TableName 
    						, a.name
    						, 'ALTER TABLE '+OBJECT_NAME(b.parent_obj)+' ADD CONSTRAINT DF_'
    												+ OBJECT_NAME(b.parent_obj)+'_'+ col_name(b.parent_obj, a.parent_column_id)
    												+ ' DEFAULT '+a.definition+ ' FOR ['+  col_name(b.parent_obj, a.parent_column_id)+']'
    						, 'ALTER TABLE '+OBJECT_NAME(b.parent_obj)+' DROP CONSTRAINT ['+ b.name+']' Index_Drop_Statement
    						, 1 OrderIndex
    						, 2 OrderIndex1
    						, 0 AS IS_Index
    						, 'DF_'+ OBJECT_NAME(b.parent_obj)+ COL_NAME(b.parent_obj, a.parent_column_id) NewIndexConstraint
    				FROM	sys.default_constraints a  (NOLOCK)
    				JOIN	sysobjects b (NOLOCK) on a.object_id = b.id
    				JOIN	#Unicode_AlterTableStructure C (NOLOCK) ON object_id(c.TableName) = a.parent_object_id and A.parent_column_id = c.colid
    						
    								
    				UNION ALL
    
    				SELECT	@l_DBName DBName
    						, OBJECT_NAME(fk.parent_object_id) AS TableName 
    						, fk.name
    						, 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(a.parent_object_id))+' ADD CONSTRAINT FK_'
    												+ OBJECT_NAME(a.parent_object_id)+'_'+OBJECT_NAME(a.referenced_object_id)
    														+'_'+col_name(a.parent_object_id, a.parent_column_id)
    												+ ' FOREIGN KEY ('+  QUOTENAME(col_name(a.parent_object_id, a.parent_column_id))
    												+' ) REFERENCES '+QUOTENAME(OBJECT_NAME(a.referenced_object_id))
    														+'( '+QUOTENAME(col_name(a.referenced_object_id, a.referenced_column_id))+')' 
    						, 'ALTER TABLE '+OBJECT_NAME(a.parent_object_id)+' DROP CONSTRAINT '+ QUOTENAME(fk.name)  Index_Drop_Statement
    						, 0 OrderIndex
    						, 1 OrderIndex1
    						, 2 AS IS_Index
    						, 'FK_'+ OBJECT_NAME(a.parent_object_id)+'_'+OBJECT_NAME(a.referenced_object_id)
    														+'_'+col_name(a.parent_object_id, a.parent_column_id) NewIndexConstraint
    						
    				
    				FROM	sys.foreign_key_columns		a  (NOLOCK)
    				JOIN	sys.foreign_keys			FK (NOLOCK) ON FK.object_id = a.CONSTRAINT_OBJECT_ID	
    				JOIN	#Unicode_AlterTableStructure C (NOLOCK) ON  object_id(c.TableName) = a.referenced_object_id 
    																and A.referenced_column_id = c.colid			
    				
    			)A		
    		
    		
    		--Step 3 : Dropping the Index & Constraints list of the depend table
    		
    		
    				
    			IF EXISTS ( SELECT TOP 1 1 FROM #Unicode_AlterIndexConstraints )
    			BEGIN
    			
    				SELECT	@l_i		=	1
    						, @l_max	= MAX(ID)
    				FROM	#Unicode_AlterIndexConstraints
    								
    				WHILE	(@l_i < = @l_max )
    				BEGIN
    						
    						SELECT		@l_sqldrop		= Index_Drop_Statement				
    									, @l_tablename		= TableName
    									, @l_indexname		= IndexName
    									, @l_isindex		= IS_Index
    						FROM	#Unicode_AlterIndexConstraints(NOLOCK)
    						WHERE	ID = @l_i
    								
    						--Foreign Key
    						
    						IF @l_isindex = 2
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''F'') IS NOT NULL'
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						--Default Constraints
    						
    						IF @l_isindex = 0
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''D'') IS NOT NULL'
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						-- Clustered / Nonclustered
    						
    						ELSE IF @l_isindex = 1
    							SELECT @l_sql = 'IF EXISTS ( SELECT 1 FROM SYSINDEXES WHERE NAME = N'''+ @l_indexname+''' AND ID = OBJECT_ID('''+@l_tablename+''')) '
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						--PRINT @l_sql
    						
    						EXEC SP_EXECUTESQL @l_sql
    						
    						SELECT	 @l_sqldrop			= NULL
    								, @l_tablename		= NULL
    								, @l_indexname		= NULL
    								, @l_isindex		= NULL
    								
    						SET @l_i = @l_i +1
    						
    				END
    			END
    
    					
    		--Step 4 : alter the table structure to NVARCHAR from NVARCHAR & NVARCHAR
    				
    			IF EXISTS ( SELECT 1 FROM #Unicode_AlterTableStructure (NOLOCK) )
    			BEGIN
    			
    				SELECT	@l_i		= 1
    						,@l_max		= MAX(ID)
    				FROM	 #Unicode_AlterTableStructure
    							
    				WHILE	( @l_i < = @l_max )
    				BEGIN
    					
    					SELECT	@l_tablename	= tablename	
    							, @l_datatype	= ' '+ NewColumnType + CASE WHEN isnullable = 0 THEN ' NOT NULL ' ELSE ' NULL ' END
    							, @l_columnname	= ColumnName
    					FROM	#Unicode_AlterTableStructure (NOLOCK)
    					WHERE	ID = @l_i
    					
    					--Removing the Statistics depends to column
    					
    					WHILE ISNULL((	SELECT	TOP 1 1 
    									FROM	sys.stats AS st (NOLOCK) 
    									JOIN	sys.tables AS t	(NOLOCK) ON st.object_id = t.object_id
    									JOIN	#Unicode_AlterTableStructure A ON object_id(a.TableName) = t.object_id
    									WHERE   st.user_created = 1
    											AND t.name = @l_tablename ),0) = 1
    					BEGIN
    						
    						SELECT @l_sql = ''
    						
    						SELECT 	TOP 1 @l_sql =  'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    								+ '.' + QUOTENAME(t.name) + '.' + QUOTENAME(st.name) 
    						FROM	sys.stats AS st (NOLOCK) 
    						JOIN	sys.tables AS t	(NOLOCK) ON st.object_id = t.object_id
    						JOIN	#Unicode_AlterTableStructure A (NOLOCK) ON object_id(a.TableName) = t.object_id
    						WHERE   st.user_created = 1
    								AND t.name = @l_tablename
    						
    						--print @l_sql
    						EXEC SP_EXECUTESQL @l_sql
    						
    					END
    							
    					-- Restructure the column datatype 
    			
    					SELECT @l_sql = 'IF EXISTS ( SELECT 1 FROM SYSCOLUMNS WHERE NAME = N'''+ @l_columnname+''' AND ID = OBJECT_ID('''+@l_tablename+'''))'
    							+CHAR(10)+'   '+' ALTER TABLE '+QUOTENAME(@l_tablename)+' ALTER COLUMN '+ QUOTENAME(@l_columnname) + @l_datatype
    					
    						--print @l_sql
    					EXEC SP_EXECUTESQL @l_sql
    					
    					SET @l_i = @l_i + 1
    
    				END				
    			END
    					
    	
    		
    		
    		--Step : 5 Recreate the Same index & consraints list of the depend table
    		
    			IF EXISTS ( SELECT TOP 1 1 FROM #Unicode_AlterIndexConstraints )
    			BEGIN
    			
    				SELECT	@l_i		=	1
    						, @l_max	= MAX(RID)
    				FROM	#Unicode_AlterIndexConstraints
    								
    				WHILE	( @l_i < = @l_max )
    				BEGIN
    									
    						SELECT	 @l_sqlcreate		= Index_Create_Statement
    								, @l_tablename		= TableName
    								, @l_indexname		= NewIndexConstraint
    								, @l_isindex		= IS_Index
    						FROM	#Unicode_AlterIndexConstraints (NOLOCK)
    						WHERE	RID = @l_i
    						
    						-- Clustered / Nonclustered Index
    						
    						IF @l_isindex = 1	
    						
    							SELECT @l_sql = 'IF NOT EXISTS ( SELECT 1 FROM SYSINDEXES WHERE NAME = N'''+ @l_indexname+''' AND ID = OBJECT_ID('''+@l_tablename+''')) '
    											+ CHAR(10)+'    ' +@l_sqlcreate + CHAR(10)
    						
    						--Foreign Key
    						
    						ELSE IF @l_isindex = 2
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''F'') IS NULL'
    											+CHAR(10)+'    '+ @l_sqlcreate+ CHAR(10)
    						
    						--Default Key
    						
    						ELSE IF @l_isindex = 0
    							SELECT @l_sql = 'IF OBJECT_ID ('''+@l_indexname +''',''D'') IS NULL'
    											+CHAR(10)+'    '+ @l_sqlcreate + CHAR(10)
    						
    						--PRINT @l_sql
    						EXEC SP_EXECUTESQL @l_sql
    						
    						
    						SELECT @l_sqlcreate			= NULL
    								, @l_tablename		= NULL
    								, @l_indexname		= NULL
    								, @l_isindex		= NULL
    						
    						SET @l_i = @l_i + 1
    						
    				END			
    				
    			END
    			
    				
    				
    		--Step : 7  Refresh NVARCHAR & NVARCHAR depends Views
    		
    		DECLARE @ActualView NVARCHAR(255)
    
    		DECLARE viewlist CURSOR FAST_FORWARD
    		FOR
    			
    			SELECT	DISTINCT TABLE_SCHEMA + '.' + TABLE_NAME AS ViewName		
    			from	INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    			WHERE	TABLE_NAME IN ( SELECT	v.name
    									FROM	sys.views v (NOLOCK)
    									JOIN	sys.sql_expression_dependencies d (NOLOCK) ON v.object_id = d.referencing_id
    									WHERE	d.is_schema_bound_reference = 0	
    											and d.referenced_entity_name IN (SELECT TABLE_NAME	
    																			 FROM	#Unicode_AlterTableStructure (NOLOCK)))
    					AND DATA_TYPE in ('NVARCHAR','NVARCHAR')
    
    			OPEN viewlist
    
    			FETCH NEXT FROM viewlist 
    			INTO @ActualView
    
    				WHILE @@FETCH_STATUS = 0
    				BEGIN
    
    					PRINT @ActualView
    					EXEC sp_refreshview @ActualView
    					
    					FETCH NEXT FROM viewlist
    					INTO @ActualView
    					
    				END
    
    			CLOSE viewlist
    			DEALLOCATE viewlist
    			
    			
    			--Table Processed Flag updation
    			
    			UPDATE	Unicode_TableRecordCount
    			SET		IsProcessed = 1
    			WHERE	TableName IN ( SELECT TableName  FROM #Unicode_AlterTableStructure )
    					AND IsProcessed = 0
    		
    			INSERT INTO Unicode_AlterIndexConstraints
    				(ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint )
    			SELECT	ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint
    			FROM	#Unicode_AlterIndexConstraints
    				
    							
    		COMMIT TRAN
    
    
    END TRY
    
    BEGIN CATCH
    	
    	SELECT 'Script on error' AS ErrMsg,ERROR_MESSAGE(), @l_sql 	
    	ROLLBACK TRAN
    	RETURN
    
    END CATCH
    END
    GO


    • Edited by Parivallal S Saturday, October 24, 2015 8:21 AM
    • Marked as answer by Parivallal S Sunday, November 29, 2015 12:13 PM
    Saturday, October 24, 2015 8:19 AM

All replies

  • I would use Aaron's stored procedure to look for VARCHAR as a string ( to be replaced with NVARCHAR)

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 13, 2015 9:38 AM
  • Finally i have written a stored procedure to change nvarchar datatype whereever char and varchar datatype defined columns by passing the table name as input.

    Steps followed for nvarchar changes

    1. drop the indexes and constriants

    2 .alter the table structures with nvarchar

    3. recreate the indexes & Constraints


    Correct me, Is there any approach to get a solution

    CREATE PROCEDURE [dbo].[Mig_ObjectWise_DataTypeChange]
    @p_TableName			NVARCHAR(200) =''
    AS
    BEGIN
    
     
    SET NOCOUNT ON
    
    	DECLARE		 @l_i						INT
    				, @l_max					INT
    				, @l_tablename				NVARCHAR(200)
    				, @l_columnname				NVARCHAR(50)
    				, @l_datatype				NVARCHAR(50)
    				, @l_DTNULL					NVARCHAR(10)
    				, @l_indexname				NVARCHAR(200)
    				, @l_isindex				TINYINT					
    				, @l_sqlcreate				NVARCHAR(4000)
    				, @l_sqldrop				NVARCHAR(4000)
    				, @l_sql					NVARCHAR(MAX)
    				, @l_DBName					NVARCHAR(200)
    		
    		
    		IF OBJECT_ID('Tempdb..#Unicode_AlterTableStructure') IS NOT NULL
    			DROP TABLE #Unicode_AlterTableStructure
    				
    		CREATE TABLE #Unicode_AlterTableStructure
    				( 	
    					ID							INT					
    					, TableName					NVARCHAR(500)
    					, ColumnName				NVARCHAR(MAX)
    					, ColumnType				NVARCHAR(MAX)
    					, length					SMALLINT
    					, isnullable				INT
    					, ExistingColumnType		NVARCHAR(500)
    					, NewColumnType				NVARCHAR(500)
    					, Foreignkey				INT					
    					, OBJECTID					INT
    					, colid						SMALLINT
    				)
    		
    		IF OBJECT_ID('tempdb..#Unicode_AlterIndexConstraints') IS NOT NULL
    			DROP TABLE #Unicode_AlterIndexConstraints
    					
    		CREATE TABLE #Unicode_AlterIndexConstraints
    				(	
    					ID							BIGINT 
    					, RID						BIGINT
    					, DBName					NVARCHAR(200)
    					, TableName					NVARCHAR(500)
    					, IndexName					NVARCHAR(MAX)
    					, Index_Create_Statement	NVARCHAR(MAX)
    					, Index_Drop_Statement		NVARCHAR(MAX)
    					, OrderIndex				TINYINT
    					, OrderIndex1				TINYINT
    					, IS_Index					INT
    					, NewIndexConstraint		NVARCHAR(MAX)				
    				)	
    		
    		
    	BEGIN TRY
    	
    		BEGIN TRAN	
    			
    				SET @l_DBName = DB_NAME()
    		
    	--Step : 1 Impact Column for the given tables	
    	
    	 IF EXISTS ( SELECT TOP 1 1 FROM sys.foreign_key_columns 
    						WHERE parent_object_id = OBJECT_ID(@p_TableName)  OR referenced_object_id = OBJECT_ID(@p_TableName)  )		
    		
    		BEGIN
    			
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )		
    						
    			SELECT 	row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID))ID, *
    			FROM		(
    					SELECT	DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'
    														  ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType
    									, CASE WHEN fk.referenced_object_id IS NOT NULL THEN 0 ELSE 1 END AS Foreignkey							
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    					FROM	SYSCOLUMNS					SC  (NOLOCK)
    					JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    					JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id	
    					LEFT JOIN sys.foreign_key_columns	FK	(NOLOCK) ON FK.referenced_object_id		= tb.object_id	 
    					WHERE	ST.NAME IN ('CHAR','VARCHAR')						
    							and SC.id IN (	SELECT	DISTINCT referenced_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.referenced_column_id = SC.colid and FC.referenced_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	referenced_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	parent_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.constraint_column_id = SC.colid  and FC.parent_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	referenced_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	referenced_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.referenced_column_id = SC.colid  and FC.referenced_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	parent_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR')
    											UNION all
    											SELECT	parent_object_id
    											FROM	sys.foreign_key_columns  FC
    											JOIN	SYSCOLUMNS				 SC  (NOLOCK) ON FC.constraint_column_id = SC.colid and FC.parent_object_id = sc.id
    											JOIN	SYSTYPES				 ST	(NOLOCK) ON ST.XTYPE = SC.xtype
    											WHERE	parent_object_id = OBJECT_ID(@p_TableName)
    													AND ST.NAME IN ('CHAR','VARCHAR'))	) A
    													
    			IF NOT EXISTS ( SELECT 1 FROM #Unicode_AlterTableStructure )										
    				
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )										
    			SELECT 	 row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID) )ID, *
    			FROM	(
    				
    						SELECT		DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'														  		
    														 ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType	
    									, 0	Foreignkey				
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    						FROM	SYSCOLUMNS					SC  (NOLOCK)
    						JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    						JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id		
    						WHERE	ST.NAME IN ('CHAR','VARCHAR') 						
    								and SC.id = OBJECT_ID(@p_TableName) 
    				) A
    													
    		END	
    		ELSE 
    			
    			INSERT INTO #Unicode_AlterTableStructure
    					( ID, TableName, ColumnName, ColumnType
    						, length, isnullable, ExistingColumnType
    						, NewColumnType, Foreignkey, OBJECTID, colid )										
    			SELECT 	 row_number() OVER(ORDER BY OBJECT_NAME(OBJECTID) )ID, *
    			FROM	(
    				
    						SELECT		DISTINCT OBJECT_NAME(SC.id) TableName
    									, SC.name ColumnName
    									, ST.NAME ColumnType
    									, SC.length
    									, SC.isnullable						
    									, CASE	WHEN ST.NAME IN ('CHAR','VARCHAR')
    											then ST.NAME +'('+CONVERT(NVARCHAR(4),SC.length)+')'								
    											ELSE ST.NAME END AS ExistingColumnType	
    									, 'NVARCHAR'+'('+CASE WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length BETWEEN 4000 AND 7999	THEN 'MAX' 
    														  WHEN ST.NAME IN ('CHAR','VARCHAR') AND SC.length  in ('-1','MAX')		THEN 'MAX'
    														  
    														  ELSE CONVERT(NVARCHAR(4),SC.length) END + ')'  NewColumnType	
    									, 0	Foreignkey				
    									, SC.id	AS OBJECTID	
    									, SC.colid			
    						FROM	SYSCOLUMNS					SC  (NOLOCK)
    						JOIN	SYSTYPES					ST	(NOLOCK) ON ST.XTYPE					= SC.xtype
    						JOIN	SYS.tables					tb	(NOLOCK) ON tb.object_id				= sc.id		
    						WHERE	ST.NAME IN ('CHAR','VARCHAR') 						
    								and SC.id = OBJECT_ID(@p_TableName) 
    				) A
    				
    					
    	--Step 2 : Taking the index & constraints for the given table
    		
    		INSERT INTO #Unicode_AlterIndexConstraints
    				(ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint )		
    	
    		SELECT		ROW_NUMBER() OVER(ORDER BY OrderIndex)ID			-- remove the index & constraints based on ID
    					, ROW_NUMBER() OVER(ORDER BY OrderIndex1 )RID		-- recreate the index & constraints based on RID
    					, *		
    		FROM		(
    		
    				SELECT		@l_DBName DBName
    							, OBJECT_NAME(A.[object_id]) AS TableName
    							, A.[Name] As IndexName
    							, CAST(
    									Case	When A.type = 1 AND is_unique = 1 Then 'CREATE UNIQUE CLUSTERED INDEX ' 
    											When A.type = 1 AND is_unique = 0 Then 'CREATE CLUSTERED INDEX ' 
    											When A.type = 2 AND is_unique = 1 Then 'CREATE UNIQUE NONCLUSTEREd INDEX '
    											When A.type = 2 AND is_unique = 0 Then 'CREATE NONCLUSTERED INDEX '
    									End
    									--+ quotename(A.[Name]) 
    									+quotename(CASE WHEN A.[Name] LIKE '%__%' AND is_primary_key = 1 THEN 
    													'PK_'+OBJECT_NAME(A.[object_id])+'_'+
    														Stuff((		Select
    																			'_' + COL_NAME(A.[object_id],C.column_id)
    																			--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    																	From	sys.index_columns C WITH (NOLOCK)																															
    																	Where	A.[Object_ID] = C.object_id
    																			And A.Index_ID = C.Index_ID
    																			And C.is_included_column = 0
    																	Order by C.key_Ordinal Asc
    																	For XML Path('') ),1,1,'')
    													 WHEN A.[Name] LIKE '%__%' AND is_unique_constraint = 1 THEN 
    													 'UK_'+OBJECT_NAME(A.[object_id])+'_'+
    															Stuff((		Select
    																				'_' + COL_NAME(A.[object_id],C.column_id)
    																				--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    																		From	sys.index_columns C WITH (NOLOCK)																																				
    																		Where	A.[Object_ID] = C.object_id
    																				And A.Index_ID = C.Index_ID
    																				And C.is_included_column = 0
    																		Order by C.key_Ordinal Asc
    																		For XML Path('') ) ,1,1,'')
    														ELSE A.[Name] END)
    									
    									
    									+ ' ON ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ' ('
    									+ Stuff(
    											(
    												Select
    														',[' + COL_NAME(A.[object_id],C.column_id)+']'
    														--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    												From	sys.index_columns C WITH (NOLOCK)												
    												Where	A.[Object_ID] = C.object_id
    														And A.Index_ID = C.Index_ID
    														And C.is_included_column = 0
    												Order by C.key_Ordinal Asc
    												For XML Path('')
    											)
    											,1,1,'') + ') '
    									+ CASE WHEN A.type = 1 THEN ''
    											ELSE Coalesce('INCLUDE ('
    													+ Stuff(
    															(
    																Select
    																		',' + QuoteName(COL_NAME(A.[object_id],C.column_id))
    																From	sys.index_columns C WITH (NOLOCK)
    																Where	A.[Object_ID] = C.object_id
    																		And A.Index_ID = C.Index_ID
    																		And C.is_included_column = 1
    																Order by C.index_column_id Asc
    																For XML Path('')
    															)
    															,1,1,'') + ') '
    										,'') End As nvarchar(Max)) As Index_Create_Statement,
    							CASE WHEN is_primary_key = 1 THEN 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(A.[object_id])) + ' DROP CONSTRAINT ' +quotename(A.[Name])
    								WHEN is_unique_constraint = 1 THEN 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(A.[object_id])) + ' DROP CONSTRAINT ' +quotename(A.[Name])
    								ELSE 'DROP INDEX ' + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' 
    										+ quotename(OBJECT_NAME(A.[object_id])) + ';'END AS Index_Drop_Statement,
    							CASE WHEN ( is_primary_key = 1 or is_unique = 1 ) THEN 2 ELSE 3 END OrderIndex 
    							, CASE WHEN ( is_primary_key = 1 or is_unique = 1 ) THEN 0 ELSE 3 END OrderIndex1 
    							, 1 AS IS_Index
    							,CASE WHEN A.[Name] LIKE '%__%' AND is_primary_key = 1 THEN 
    									'PK_'+OBJECT_NAME(A.[object_id])+'_'+
    										Stuff((		Select
    															'_' + COL_NAME(A.[object_id],C.column_id)
    															--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    													From	sys.index_columns C WITH (NOLOCK)													
    													Where	A.[Object_ID] = C.object_id
    															And A.Index_ID = C.Index_ID
    															And C.is_included_column = 0
    													Order by C.key_Ordinal Asc
    													For XML Path('') ),1,1,'')
    								 WHEN A.[Name] LIKE '%__%' AND is_unique_constraint = 1 THEN 
    								 'UK_'+OBJECT_NAME(A.[object_id])+'_'+
    										Stuff((		Select
    															'_' + COL_NAME(A.[object_id],C.column_id)
    															--+ Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End
    													From	sys.index_columns C WITH (NOLOCK)													
    													Where	A.[Object_ID] = C.object_id
    															And A.Index_ID = C.Index_ID
    															And C.is_included_column = 0
    													Order by C.key_Ordinal Asc
    													For XML Path('') ) ,1,1,'')
    								ELSE A.[Name] END AS NewIndexConstraint
    				From		SYS.Indexes A WITH (NOLOCK)
    				INNER JOIN	sys.objects B WITH (NOLOCK)	ON	A.object_id = B.object_id
    				INNER JOIN 	SYS.schemas S (NOLOCK)	ON	B.schema_id = S.schema_id            			     
    				Where		A.type IN (1,2) --clustered and nonclustered
    							AND B.Type != 'S'	 				
    							AND EXISTS (		SELECT	1
    												From	sys.index_columns	C WITH (NOLOCK)												
    												JOIN	#Unicode_AlterTableStructure UCC WITH (NOLOCK) 
    																			ON Object_id(ucc.TableName) = c.object_id and ucc.colid = c.column_id 
    																			AND c.index_id = a.index_id  AND A.object_id = Object_id(ucc.TableName))
    				
    				UNION ALL
    
    				SELECT	@l_DBName DBName
    						, OBJECT_NAME(b.parent_obj) AS TableName 
    						, a.name
    						, 'ALTER TABLE '+OBJECT_NAME(b.parent_obj)+' ADD CONSTRAINT DF_'
    												+ OBJECT_NAME(b.parent_obj)+'_'+ col_name(b.parent_obj, a.parent_column_id)
    												+ ' DEFAULT '+a.definition+ ' FOR ['+  col_name(b.parent_obj, a.parent_column_id)+']'
    						, 'ALTER TABLE '+OBJECT_NAME(b.parent_obj)+' DROP CONSTRAINT ['+ b.name+']' Index_Drop_Statement
    						, 1 OrderIndex
    						, 2 OrderIndex1
    						, 0 AS IS_Index
    						, 'DF_'+ OBJECT_NAME(b.parent_obj)+ COL_NAME(b.parent_obj, a.parent_column_id) NewIndexConstraint
    				FROM	sys.default_constraints a  (NOLOCK)
    				JOIN	sysobjects b (NOLOCK) on a.object_id = b.id
    				JOIN	#Unicode_AlterTableStructure C (NOLOCK) ON object_id(c.TableName) = a.parent_object_id and A.parent_column_id = c.colid
    						
    								
    				UNION ALL
    
    				SELECT	@l_DBName DBName
    						, OBJECT_NAME(fk.parent_object_id) AS TableName 
    						, fk.name
    						, 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(a.parent_object_id))+' ADD CONSTRAINT FK_'
    												+ OBJECT_NAME(a.parent_object_id)+'_'+OBJECT_NAME(a.referenced_object_id)
    														+'_'+col_name(a.parent_object_id, a.parent_column_id)
    												+ ' FOREIGN KEY ('+  QUOTENAME(col_name(a.parent_object_id, a.parent_column_id))
    												+' ) REFERENCES '+QUOTENAME(OBJECT_NAME(a.referenced_object_id))
    														+'( '+QUOTENAME(col_name(a.referenced_object_id, a.referenced_column_id))+')' 
    						, 'ALTER TABLE '+OBJECT_NAME(a.parent_object_id)+' DROP CONSTRAINT '+ QUOTENAME(fk.name)  Index_Drop_Statement
    						, 0 OrderIndex
    						, 1 OrderIndex1
    						, 2 AS IS_Index
    						, 'FK_'+ OBJECT_NAME(a.parent_object_id)+'_'+OBJECT_NAME(a.referenced_object_id)
    														+'_'+col_name(a.parent_object_id, a.parent_column_id) NewIndexConstraint
    						
    				
    				FROM	sys.foreign_key_columns		a  (NOLOCK)
    				JOIN	sys.foreign_keys			FK (NOLOCK) ON FK.object_id = a.CONSTRAINT_OBJECT_ID	
    				JOIN	#Unicode_AlterTableStructure C (NOLOCK) ON  object_id(c.TableName) = a.referenced_object_id 
    																and A.referenced_column_id = c.colid			
    				
    			)A		
    		
    		
    		--Step 3 : Dropping the Index & Constraints list of the depend table
    		
    		
    				
    			IF EXISTS ( SELECT TOP 1 1 FROM #Unicode_AlterIndexConstraints )
    			BEGIN
    			
    				SELECT	@l_i		=	1
    						, @l_max	= MAX(ID)
    				FROM	#Unicode_AlterIndexConstraints
    								
    				WHILE	(@l_i < = @l_max )
    				BEGIN
    						
    						SELECT		@l_sqldrop		= Index_Drop_Statement				
    									, @l_tablename		= TableName
    									, @l_indexname		= IndexName
    									, @l_isindex		= IS_Index
    						FROM	#Unicode_AlterIndexConstraints(NOLOCK)
    						WHERE	ID = @l_i
    								
    						--Foreign Key
    						
    						IF @l_isindex = 2
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''F'') IS NOT NULL'
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						--Default Constraints
    						
    						IF @l_isindex = 0
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''D'') IS NOT NULL'
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						-- Clustered / Nonclustered
    						
    						ELSE IF @l_isindex = 1
    							SELECT @l_sql = 'IF EXISTS ( SELECT 1 FROM SYSINDEXES WHERE NAME = N'''+ @l_indexname+''' AND ID = OBJECT_ID('''+@l_tablename+''')) '
    											+CHAR(10)+'    '+ @l_sqldrop+ CHAR(10)
    						
    						--PRINT @l_sql
    						
    						EXEC SP_EXECUTESQL @l_sql
    						
    						SELECT	 @l_sqldrop			= NULL
    								, @l_tablename		= NULL
    								, @l_indexname		= NULL
    								, @l_isindex		= NULL
    								
    						SET @l_i = @l_i +1
    						
    				END
    			END
    
    					
    		--Step 4 : alter the table structure to NVARCHAR from NVARCHAR & NVARCHAR
    				
    			IF EXISTS ( SELECT 1 FROM #Unicode_AlterTableStructure (NOLOCK) )
    			BEGIN
    			
    				SELECT	@l_i		= 1
    						,@l_max		= MAX(ID)
    				FROM	 #Unicode_AlterTableStructure
    							
    				WHILE	( @l_i < = @l_max )
    				BEGIN
    					
    					SELECT	@l_tablename	= tablename	
    							, @l_datatype	= ' '+ NewColumnType + CASE WHEN isnullable = 0 THEN ' NOT NULL ' ELSE ' NULL ' END
    							, @l_columnname	= ColumnName
    					FROM	#Unicode_AlterTableStructure (NOLOCK)
    					WHERE	ID = @l_i
    					
    					--Removing the Statistics depends to column
    					
    					WHILE ISNULL((	SELECT	TOP 1 1 
    									FROM	sys.stats AS st (NOLOCK) 
    									JOIN	sys.tables AS t	(NOLOCK) ON st.object_id = t.object_id
    									JOIN	#Unicode_AlterTableStructure A ON object_id(a.TableName) = t.object_id
    									WHERE   st.user_created = 1
    											AND t.name = @l_tablename ),0) = 1
    					BEGIN
    						
    						SELECT @l_sql = ''
    						
    						SELECT 	TOP 1 @l_sql =  'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) 
    								+ '.' + QUOTENAME(t.name) + '.' + QUOTENAME(st.name) 
    						FROM	sys.stats AS st (NOLOCK) 
    						JOIN	sys.tables AS t	(NOLOCK) ON st.object_id = t.object_id
    						JOIN	#Unicode_AlterTableStructure A (NOLOCK) ON object_id(a.TableName) = t.object_id
    						WHERE   st.user_created = 1
    								AND t.name = @l_tablename
    						
    						--print @l_sql
    						EXEC SP_EXECUTESQL @l_sql
    						
    					END
    							
    					-- Restructure the column datatype 
    			
    					SELECT @l_sql = 'IF EXISTS ( SELECT 1 FROM SYSCOLUMNS WHERE NAME = N'''+ @l_columnname+''' AND ID = OBJECT_ID('''+@l_tablename+'''))'
    							+CHAR(10)+'   '+' ALTER TABLE '+QUOTENAME(@l_tablename)+' ALTER COLUMN '+ QUOTENAME(@l_columnname) + @l_datatype
    					
    						--print @l_sql
    					EXEC SP_EXECUTESQL @l_sql
    					
    					SET @l_i = @l_i + 1
    
    				END				
    			END
    					
    	
    		
    		
    		--Step : 5 Recreate the Same index & consraints list of the depend table
    		
    			IF EXISTS ( SELECT TOP 1 1 FROM #Unicode_AlterIndexConstraints )
    			BEGIN
    			
    				SELECT	@l_i		=	1
    						, @l_max	= MAX(RID)
    				FROM	#Unicode_AlterIndexConstraints
    								
    				WHILE	( @l_i < = @l_max )
    				BEGIN
    									
    						SELECT	 @l_sqlcreate		= Index_Create_Statement
    								, @l_tablename		= TableName
    								, @l_indexname		= NewIndexConstraint
    								, @l_isindex		= IS_Index
    						FROM	#Unicode_AlterIndexConstraints (NOLOCK)
    						WHERE	RID = @l_i
    						
    						-- Clustered / Nonclustered Index
    						
    						IF @l_isindex = 1	
    						
    							SELECT @l_sql = 'IF NOT EXISTS ( SELECT 1 FROM SYSINDEXES WHERE NAME = N'''+ @l_indexname+''' AND ID = OBJECT_ID('''+@l_tablename+''')) '
    											+ CHAR(10)+'    ' +@l_sqlcreate + CHAR(10)
    						
    						--Foreign Key
    						
    						ELSE IF @l_isindex = 2
    							
    							SELECT @l_sql = 'IF OBJECT_ID('''+@l_indexname +''',''F'') IS NULL'
    											+CHAR(10)+'    '+ @l_sqlcreate+ CHAR(10)
    						
    						--Default Key
    						
    						ELSE IF @l_isindex = 0
    							SELECT @l_sql = 'IF OBJECT_ID ('''+@l_indexname +''',''D'') IS NULL'
    											+CHAR(10)+'    '+ @l_sqlcreate + CHAR(10)
    						
    						--PRINT @l_sql
    						EXEC SP_EXECUTESQL @l_sql
    						
    						
    						SELECT @l_sqlcreate			= NULL
    								, @l_tablename		= NULL
    								, @l_indexname		= NULL
    								, @l_isindex		= NULL
    						
    						SET @l_i = @l_i + 1
    						
    				END			
    				
    			END
    			
    				
    				
    		--Step : 7  Refresh NVARCHAR & NVARCHAR depends Views
    		
    		DECLARE @ActualView NVARCHAR(255)
    
    		DECLARE viewlist CURSOR FAST_FORWARD
    		FOR
    			
    			SELECT	DISTINCT TABLE_SCHEMA + '.' + TABLE_NAME AS ViewName		
    			from	INFORMATION_SCHEMA.COLUMNS (NOLOCK)
    			WHERE	TABLE_NAME IN ( SELECT	v.name
    									FROM	sys.views v (NOLOCK)
    									JOIN	sys.sql_expression_dependencies d (NOLOCK) ON v.object_id = d.referencing_id
    									WHERE	d.is_schema_bound_reference = 0	
    											and d.referenced_entity_name IN (SELECT TABLE_NAME	
    																			 FROM	#Unicode_AlterTableStructure (NOLOCK)))
    					AND DATA_TYPE in ('NVARCHAR','NVARCHAR')
    
    			OPEN viewlist
    
    			FETCH NEXT FROM viewlist 
    			INTO @ActualView
    
    				WHILE @@FETCH_STATUS = 0
    				BEGIN
    
    					PRINT @ActualView
    					EXEC sp_refreshview @ActualView
    					
    					FETCH NEXT FROM viewlist
    					INTO @ActualView
    					
    				END
    
    			CLOSE viewlist
    			DEALLOCATE viewlist
    			
    			
    			--Table Processed Flag updation
    			
    			UPDATE	Unicode_TableRecordCount
    			SET		IsProcessed = 1
    			WHERE	TableName IN ( SELECT TableName  FROM #Unicode_AlterTableStructure )
    					AND IsProcessed = 0
    		
    			INSERT INTO Unicode_AlterIndexConstraints
    				(ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint )
    			SELECT	ID, RID, DBName, TableName, IndexName
    					, Index_Create_Statement, Index_Drop_Statement		
    					, OrderIndex, OrderIndex1, IS_Index, NewIndexConstraint
    			FROM	#Unicode_AlterIndexConstraints
    				
    							
    		COMMIT TRAN
    
    
    END TRY
    
    BEGIN CATCH
    	
    	SELECT 'Script on error' AS ErrMsg,ERROR_MESSAGE(), @l_sql 	
    	ROLLBACK TRAN
    	RETURN
    
    END CATCH
    END
    GO


    • Edited by Parivallal S Saturday, October 24, 2015 8:21 AM
    • Marked as answer by Parivallal S Sunday, November 29, 2015 12:13 PM
    Saturday, October 24, 2015 8:19 AM