Looping through all tables and then looping through all column datatypes

Question

I am creating a stored procedure in Microsoft SQL Server Management Studio and I want to loop through all the table names and then loop through all the columns and catch their datatype, when the datatype is ntext i want it to alter the datatype into nvarchar(max)

 USE [fmsStage]
 GO


/****** Object: StoredProcedure [dbo].[removenText] Script Date 10/27/2016 8:35:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spRemovenText]
AS

BEGIN
BEGIN TRANSACTION
    DECLARE @Table_Name nvarchar(50)
    DECLARE @Query nvarchar(250)
    DECLARE Table_Cursor CURSOR FOR SELECT Name FROM sys.tables ORDER BY Name ASC;
    DECLARE @Affected_Rows int = 0

    OPEN Table_Cursor
    FETCH NEXT FROM Table_Cursor INTO @Table_Name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        print @Table_Name
        ALTER TABLE [fmsStage].[dbo].@Table_Name ALTER COLUMN
        FETCH NEXT FROM Table_Cursor INTO @Table_Name
    END
    SELECT @Affected_Rows AS Affected_Rows

    CLOSE  Table_Cursor
    DEALLOCATE Table_Cursor

    ROLLBACK 
END

And print @Table_Name gives me back all the table names. But I can't figure out how to loop through all the field/column names to get their data type and when the datatype is ntext change them to nvarchar(max)

UPDATE: I've now got two loops inside of each other. And it gives me back all data types. But it doesn't give me back the column name because there can't be 2 select parts in this (Sorry for my poor explanation) This is my current code:

USE [fmsStage]
GO


/****** Object: StoredProcedure [dbo].[removenText] Script Date 10/27/2016 8:35:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spRemovenText]
AS

BEGIN
BEGIN TRANSACTION
    DECLARE @Table_Name nvarchar(50)
    DECLARE @Column_Name nvarchar(50)
    DECLARE @Query nvarchar(250)
    DECLARE Table_Cursor CURSOR FOR SELECT Name FROM sys.tables ORDER BY Name ASC;
    DECLARE @Affected_Rows int = 0
    DECLARE @sql nvarchar(max)
    /******* Cursor for Loop 1 ******/
    OPEN Table_Cursor
    FETCH NEXT FROM Table_Cursor INTO @Table_Name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        print @Table_Name
        /******* Cursor for Loop 2 *******/
        DECLARE Column_Cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;
        OPEN Column_Cursor
        FETCH NEXT FROM Column_Cursor INTO @Column_Name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            print @Column_Name

            FETCH NEXT FROM Column_Cursor INTO @Column_Name
        END
        CLOSE  Column_Cursor
        DEALLOCATE Column_Cursor
        FETCH NEXT FROM Table_Cursor INTO @Table_Name
    END

    CLOSE  Table_Cursor
    DEALLOCATE Table_Cursor

    ROLLBACK 
END

When i change:

DECLARE Column_Cursor CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;

Into this:

DECLARE Column_Cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;

Or this:

DECLARE Column_Cursor CURSOR FOR SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;

It gives me back either the datatype or the column name (Depending on what I am actually selecting) But i want it to give back both things. Not just Data type or Column name

To get information like that, do research in [INFORMATION_SCHEMA](https://msdn.microsoft.com/en-us/library/ms186778.aspx) views. Also, your ALTER statements will have to be Dynamic SQL: SQL does not allow for variable table names amongst others. A dynamic SQL statement is one your build up in a `NVARCHAR(...)`, and run using `EXECUTE (@sql);` or `EXEC sp_executesql @sql;` — TT., Oct 27 '16 at 07:57
Thank you TT. I have now got this query: SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name But i dont know how to make it loop inside the other loop — Thimo Franken, Oct 27 '16 at 07:58
You can have two cursors, one for the outer loop, one for the inner loop (table columns). Don't forget to close/deallocate properly for the inner loop. — TT., Oct 27 '16 at 07:59
Do i then DECLARE the second cursor inside the first cursor? because otherwise @Table_Name doesnt even exist yet. — Thimo Franken, Oct 27 '16 at 08:01
Looking at your problem, it's probably easier to do without loops. Lemme see if I can cook up something. — TT., Oct 27 '16 at 08:04

TT. · Accepted Answer · 2016-10-27T09:39:50.437

5

You can build the statements in a string and execute it dynamically. That way you don't have a need for while-loops.

DECLARE @alter_stmts NVARCHAR(MAX) = (
    SELECT 
        ';ALTER TABLE '+
            QUOTENAME(t.TABLE_SCHEMA)+'.'+QUOTENAME(t.TABLE_NAME)+' '+
        'ALTER COLUMN '+
            QUOTENAME(c.COLUMN_NAME)+ ' NVARCHAR(MAX) ' + CASE WHEN c.IS_NULLABLE='YES' THEN 'NULL' ELSE 'NOT NULL' END 
    FROM 
        INFORMATION_SCHEMA.TABLES AS t
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
            c.TABLE_SCHEMA=t.TABLE_SCHEMA AND
            c.TABLE_NAME=t.TABLE_NAME
    WHERE
        t.TABLE_TYPE='BASE TABLE' AND
        c.DATA_TYPE LIKE '%NTEXT%'
    FOR
        XML PATH('')
)+';';

--SELECT @alter_stmts; -- review
EXEC sp_executesql @alter_stmts; -- execute it

edited Oct 27 '16 at 09:39

answered Oct 27 '16 at 08:11

TT.

15,774
6
47
88

This only changes 1 table, i need to loop through ALL tables in a gigantic database and change all ntext to nvarchar – Thimo Franken Oct 27 '16 at 08:14
I've changed the original post with some extra code. – Thimo Franken Oct 27 '16 at 08:15
You don't appear to have tried. Run the script without the EXEC part, and see what the @alter_stmts look like. You will see all tables that have NTEXT columns in there. – TT. Oct 27 '16 at 08:16
It has a problem. My boss wants this to be automatically and not dynamically and also the nvarchar(max) is too small for everything in my database, as i said, the database is gigantic – Thimo Franken Oct 27 '16 at 08:19
1

@TT. I like this, as it doesn't need a cursor... +1 from my side. One hint: I just edited my answer to add a check to exclude columns in views. This is necessary anyway and might shrink your statement a lot. – Shnugo Oct 27 '16 at 08:23
**(1)** How would you define the requirement "automatically"? **(2)** There is no other way to do this than using Dynamic SQL, as I already alluded to: you cannot have static SQL with a variable table name or column name. **(3)** `NVARCHAR(MAX)` can hold up to 2Gb of text... not enough you say? I say: quite enough... – TT. Oct 27 '16 at 08:23
@Shnugo Good point... I'll update as well. Thanks for the insight. (+'d you already ;-) – TT. Oct 27 '16 at 08:24
@ThimoFranken If you really fear 2Gb of text isn't enough, after the statement generation in `@alter_stmts` do a `SELECT LEN(@alter_stmts)`. If it is less than 2Gb then you need not worry... – TT. Oct 27 '16 at 08:26
1

@ThimoFranken your *too small for everything* might just be an output issue. By default the maximum length of text shown to you within the result of a `SELECT` is rather small. You can change this with a right click into the query window, choose `options` and set the text max length to 8192. Anyway, the length passed into `EXEC` should suffice... – Shnugo Oct 27 '16 at 08:26
Every `BEGIN TRANSACTION` should end with a `COMMIT TRANSACTION` or a `ROLLBACK TRANSACTION`. Apparantly you have a mismatch there. – TT. Oct 27 '16 at 08:40
@ThimoFranken Read more about that issue [here](http://stackoverflow.com/q/21930156/243373) on StackOverflow. – TT. Oct 27 '16 at 08:41
It always returns this when i execute it: The name ';ALTER TABLE [dbo].[vwTemp] ALTER COLUMN [DESCRIPTION] NVARCHAR(MAX) NULL;ALTER TABLE [dbo].[vwAccountancyOutgoingDetails] ALTER .................[vwAccountancyIncomingDetails2010] ALTER COLUMN [DESCRIPTION] NVARCHAR(MAX) NULL;ALTER TABLE [dbo].[Cargo] ALTER COLUMN [MARKSNUMBERS] NVARCHAR(MAX) NULL;ALTER TABLE [dbo].[Cargo] ALTER COLUMN [DESCRIPTION] NVARCHAR(MAX) NULL;ALTER TABLE [dbo].[stageImportTxtFile] ALTER COLUMN [MARKSNUMBERS] NVARCHAR(MAX) NULL;ALTER TABLE [dbo].[stageImportTxtFile] ALTER COLUMN [DESCRIPTI' is not a valid identifier. – Thimo Franken Oct 27 '16 at 08:57
It seems to be missing the table name. :S – Thimo Franken Oct 27 '16 at 08:59
@ThimoFranken This table name `ALTER TABLE [dbo].[vwTemp]` looks like you did not implement the necessary check for `TABLE_TYPE='BASE TABLE'`. You cannot change columns of VIEWs this way... But you might need to recompile them... – Shnugo Oct 27 '16 at 09:03
@ThimoFranken So you put the exact script from my answer in SSMS, run the whole script (minus the SELECT, because that doesn't do anything really), and that's the error message you get? – TT. Oct 27 '16 at 09:03
@Shnugo AFAICT you can't recompile views. The OP might run into problems if the views were defined with SCHEMABINDING though. – TT. Oct 27 '16 at 09:06
@TT. Yes, I meant something like `ALTER VIEW ... full statement here`... And you are right, with `SCHEMA BINDING` the generated `ALTER TABLE` statements might break... – Shnugo Oct 27 '16 at 09:15

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can create a statement like the following to create statements dynamically.

for a one time action just copy the result into a new query window, check the code and execute.
if done more often you might use a CURSOR to work this down automatically.

Try something like this:

EDIT

Added the check for TABLE_TYPE='BASE TABLE'...

SELECT 'ALTER TABLE ' + QUOTENAME(c.TABLE_CATALOG) + '.' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) 
    + ' ALTER COLUMN ' + QUOTENAME(c.COLUMN_NAME) + ' VARCHAR(MAX) ' + CASE WHEN c.IS_NULLABLE='NO' THEN 'NOT NULL ' ELSE 'NULL ' END
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE' 
  AND c.DATA_TYPE='NTEXT';

Looping through all tables and then looping through all column datatypes

2 Answers2

EDIT