SHOW:
|
|
- or go back to the newest paste.
| 1 | ALTER TABLE dbo.T ALTER COLUMN NTextColumn NVARCHAR(MAX) NULL; -- OR NOT NULL | |
| 2 | ||
| 3 | - | DECLARE @SQL NVARCHAR(MAX) = ''; |
| 3 | + | DECLARE @SQL NVARCHAR(MAX) = ''; |
| 4 | - | |
| 4 | + | |
| 5 | - | SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + |
| 5 | + | SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + |
| 6 | - | QUOTENAME(OBJECT_NAME(object_id)) + |
| 6 | + | QUOTENAME(OBJECT_NAME(object_id)) + |
| 7 | - | ' ALTER COLUMN ' + QUOTENAME(Name) + |
| 7 | + | ' ALTER COLUMN ' + QUOTENAME(Name) + |
| 8 | - | ' NVARCHAR(MAX) ' + |
| 8 | + | ' NVARCHAR(MAX) ' + |
| 9 | - | CASE WHEN is_nullable = 1 THEN 'NOT' ELSE '' END + |
| 9 | + | CASE WHEN is_nullable = 1 THEN 'NOT' ELSE '' END + |
| 10 | - | ' NULL;' + CHAR(13) + 'GO' |
| 10 | + | ' NULL;' + CHAR(13) + 'GO' |
| 11 | - | FROM sys.columns |
| 11 | + | FROM sys.columns |
| 12 | - | WHERE system_type_id = 99; --NTEXT |
| 12 | + | WHERE system_type_id = 99; --NTEXT |
| 13 | - | |
| 13 | + | |
| 14 | SP_EXECUTESQL @SQL; | |
| 15 | ||
| 16 | - | DECLARE TableCursor CURSOR FAST_FORWARD |
| 16 | + | DECLARE TableCursor CURSOR FAST_FORWARD |
| 17 | - | FOR |
| 17 | + | FOR |
| 18 | - | SELECT |
| 18 | + | SELECT |
| 19 | - | t.Name, |
| 19 | + | t.Name, |
| 20 | - | c.name, |
| 20 | + | c.name, |
| 21 | - | c.is_nullable, |
| 21 | + | c.is_nullable, |
| 22 | - | typ.user_type_id |
| 22 | + | typ.user_type_id |
| 23 | - | FROM |
| 23 | + | FROM |
| 24 | - | sys.columns c |
| 24 | + | sys.columns c |
| 25 | - | INNER JOIN |
| 25 | + | INNER JOIN |
| 26 | - | sys.tables t ON c.object_id = t.object_id |
| 26 | + | sys.tables t ON c.object_id = t.object_id |
| 27 | - | INNER JOIN |
| 27 | + | INNER JOIN |
| 28 | - | sys.types typ ON c.system_type_id = typ.system_type_id |
| 28 | + | sys.types typ ON c.system_type_id = typ.system_type_id |
| 29 | - | WHERE |
| 29 | + | WHERE |
| 30 | - | typ.name IN ('text', 'ntext') -- user_type_id: text = 35, ntext = 99 |
| 30 | + | typ.name IN ('text', 'ntext') -- user_type_id: text = 35, ntext = 99
|
| 31 | - | |
| 31 | + | |
| 32 | - | DECLARE @TableName sysname, @ColumnName sysname, @IsNullable BIT, @TypeID INT |
| 32 | + | DECLARE @TableName sysname, @ColumnName sysname, @IsNullable BIT, @TypeID INT |
| 33 | - | |
| 33 | + | |
| 34 | - | OPEN TableCursor |
| 34 | + | OPEN TableCursor |
| 35 | - | |
| 35 | + | |
| 36 | - | FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID |
| 36 | + | FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID |
| 37 | - | |
| 37 | + | |
| 38 | - | WHILE @@FETCH_STATUS = 0 |
| 38 | + | WHILE @@FETCH_STATUS = 0 |
| 39 | - | BEGIN |
| 39 | + | BEGIN |
| 40 | - | DECLARE @Stmt NVARCHAR(999) |
| 40 | + | DECLARE @Stmt NVARCHAR(999) |
| 41 | - | |
| 41 | + | |
| 42 | - | SET @Stmt = 'ALTER TABLE dbo.[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] ' + |
| 42 | + | SET @Stmt = 'ALTER TABLE dbo.[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] ' + |
| 43 | - | CASE @TypeID |
| 43 | + | CASE @TypeID |
| 44 | - | WHEN 35 THEN ' VARCHAR(MAX) ' |
| 44 | + | WHEN 35 THEN ' VARCHAR(MAX) ' |
| 45 | - | WHEN 99 THEN ' NVARCHAR(MAX) ' |
| 45 | + | WHEN 99 THEN ' NVARCHAR(MAX) ' |
| 46 | - | END + |
| 46 | + | END + |
| 47 | - | CASE WHEN @IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END |
| 47 | + | CASE WHEN @IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END |
| 48 | - | |
| 48 | + | |
| 49 | - | PRINT @Stmt |
| 49 | + | PRINT @Stmt |
| 50 | - | EXEC (@Stmt) |
| 50 | + | EXEC (@Stmt) |
| 51 | - | |
| 51 | + | |
| 52 | - | FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID |
| 52 | + | FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID |
| 53 | - | END |
| 53 | + | END |
| 54 | - | |
| 54 | + | |
| 55 | - | CLOSE TableCursor |
| 55 | + | CLOSE TableCursor |
| 56 | DEALLOCATE TableCursor | |
| 57 | ||
| 58 | - | DECLARE @SQL NVARCHAR(MAX) = ' '; |
| 58 | + | DECLARE @SQL NVARCHAR(MAX) = ' '; |
| 59 | - | |
| 59 | + | |
| 60 | - | SELECT @SQL = @SQL + ' ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.columns.object_id)) + '.' + |
| 60 | + | SELECT @SQL = @SQL + ' ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.columns.object_id)) + '.' + |
| 61 | - | QUOTENAME(OBJECT_NAME(sys.columns.object_id)) + |
| 61 | + | QUOTENAME(OBJECT_NAME(sys.columns.object_id)) + |
| 62 | - | ' ALTER COLUMN ' + QUOTENAME(sys.columns.Name) + |
| 62 | + | ' ALTER COLUMN ' + QUOTENAME(sys.columns.Name) + |
| 63 | - | ' NVARCHAR(MAX) ' + |
| 63 | + | ' NVARCHAR(MAX) ' + |
| 64 | - | CASE WHEN is_nullable = 0 THEN 'NOT NULL' ELSE '' END |
| 64 | + | CASE WHEN is_nullable = 0 THEN 'NOT NULL' ELSE '' END |
| 65 | - | |
| 65 | + | |
| 66 | - | FROM sys.Tables |
| 66 | + | FROM sys.Tables |
| 67 | - | inner join sys.columns on sys.tables.object_id = sys.columns.object_id |
| 67 | + | inner join sys.columns on sys.tables.object_id = sys.columns.object_id |
| 68 | - | WHERE sys.columns.system_type_id = 99 ; --NTEXT |
| 68 | + | WHERE sys.columns.system_type_id = 99 ; --NTEXT |
| 69 | - | |
| 69 | + | |
| 70 | - | EXECUTE sp_executesql @SQL; |
| 70 | + | EXECUTE sp_executesql @SQL; |
| 71 | GO |
