Looping through all tables and then looping through all column datatypes
sql-server, t-sql
Solution
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
Problem
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