SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sys_change_udt]
	@udt VARCHAR(150),
	@udtschema VARCHAR(150),
	@newudtschema VARCHAR(150),
	@newudtDataType VARCHAR(150),
	@newudtDataSize smallint,
	@OtherParameter VARCHAR(50)
AS

	SET NOCOUNT ON
	
	
	/*
	exec p_sys_change_udt	@udt = 'ais_decimal2', 
							@udtschema = 'dbo',
							@newudtDataType = 'ais_decimal2_new',
							@newudtDataSize = 0,
							@newudtschema = 'dbo',
							@OtherParameter = ' null'
	*/
	/*
	SET @udt = 'Name' — Existing UDDT
	SET @udtschema = 'dbo' — Schema of the UDDT
	SET @newudtDataType = 'varchar' — Data type for te new UDDT
	SET @newudtDataSize = 500 — Lenght of the new UDDT
	SET @newudtschema = 'dbo' — Schema of the new UDDT
	SET @OtherParameter = ' NULL' — Other parameters like NULL , NOT NULL
	*/
	
	DECLARE @Datatype VARCHAR(50),
		@Datasize SMALLINT

	DECLARE @varcharDataType VARCHAR(50)

	DECLARE @Schemaname VARCHAR(50),
		@TableName VARCHAR(50),
		@FiledName VARCHAR(50)

	CREATE TABLE #udtflds
		(
		  Schemaname VARCHAR(50),
		  TableName VARCHAR(50),
		  FiledName VARCHAR(50),
		  IS_Nullable VARCHAR(3)
		)

	SELECT TOP 1
			@Datatype = Data_type,
			@Datasize = character_maximum_length
	FROM    INFORMATION_SCHEMA.COLUMNS
	WHERE   Domain_name = @udt
			AND Domain_schema = @udtschema

	SET @varcharDataType = @Datatype
	IF @DataType Like '%char%'
		AND @Datasize IS NOT NULL
		AND ( @newudtDataType <> 'varchar(max)'
			  OR @newudtDataType <> 'nvarchar(max)'
			)
		BEGIN
			SET @varcharDataType = @varcharDataType + '('
				+ CAST(@Datasize AS VARCHAR(50)) + ')'
		END

	INSERT  INTO #udtflds
			SELECT  TABLE_SCHEMA,
					TABLE_NAME,
					Column_Name,
					IS_NULLABLE
			FROM    INFORMATION_SCHEMA.COLUMNS
			WHERE   Domain_name = @udt
					AND Domain_schema = @udtschema
					AND TABLE_NAME not like 'vw%'
	DECLARE @exec VARCHAR(500)

	DECLARE alter_cursor CURSOR
		FOR SELECT  Schemaname,
					TableName,
					FiledName,
					IS_NULLABLE
			FROM    #udtflds

	DECLARE @IsNullable VARCHAR(3)

	OPEN alter_cursor
	FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName, @IsNullable

	WHILE @@FETCH_STATUS = 0
		BEGIN
		
			
			SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
				+ '  ALTER COLUMN ' + @FiledName + ' ' + @newudtDataType
			
			IF @IsNullable = 'NO'
				SET @exec = @exec + ' NOT NULL'
				
			EXECUTE ( @exec )
			FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName, @IsNullable

		END

	CLOSE alter_cursor
	
	DEALLOCATE alter_cursor
	
	RETURN
	

	SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
	--EXEC ( @exec )

	SET @varcharDataType = @newudtDataType

	IF @newudtDataType Like '%char%'
		AND @newudtDataSize IS NOT NULL
		AND ( @newudtDataType <> 'varchar(max)'
			  OR @newudtDataType <> 'nvarchar(max)'
			)
		BEGIN
			SET @varcharDataType = @varcharDataType + '('
				+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'
		END

	SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
		+ @varcharDataType + ' ' + @OtherParameter
	--EXEC ( @exec )

	OPEN alter_cursor
	FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
				+ '  ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
				+ '].[' + @udt + ']'
			EXECUTE ( @exec)
			FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
		END

	CLOSE alter_cursor
	DEALLOCATE alter_cursor
	SELECT  *
	FROM    #udtflds

	DROP TABLE #udtflds
GO
