title: Looping through all tables and then looping through all column datatypes
body: <p>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)
  </p>
  
  <pre><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 @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
  </code></pre>
  
  <p>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)</p>
  
  <p>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: </p>
  
  <pre><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
  </code></pre>
  
  <p>When i change: </p>
  
  <pre><code>DECLARE Column_Cursor CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;
  </code></pre>
  
  <p>Into this: </p>
  
  <pre><code>DECLARE Column_Cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;
  </code></pre>
  
  <p>Or this: </p>
  
  <pre><code>DECLARE Column_Cursor CURSOR FOR SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name;
  </code></pre>
  
  <p>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</p>