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

Answer 1

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

Answer 2

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';