循环遍历所有表，然后循环遍历所有列数据类型

Question

我在Microsoft SQL Server Management Studio中创建了一个存储过程，我想循环所有表名，然后循环所有列并捕获它们的数据类型，当数据类型为文本时，我希望它将数据类型更改为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

print @Table_Name返回所有表名。但我不知道如何循环通过所有的字段/列名称，以获得他们的数据类型，当数据类型是文本更改为nvarchar(max)

更新:我现在有两个循环在彼此里面。它还给我所有的数据类型。但它没有给我回列名，因为不能有2选择部分在这(抱歉我的解释不好)这是我目前的代码:

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

当我改变:

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;

或者:

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

它给了我返回的数据类型或列名(取决于我实际选择)，但我希望它给回这两件事。不只是数据类型或列名

Answer 1

您可以在字符串中构建语句并动态执行它。这样你就不需要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

可以创建如下语句动态创建语句

• 对于一次性操作，只需将结果复制到新的查询窗口，检查代码并执行。

• 如果你经常这样做，你可以使用CURSOR来自动完成。

试试这样写:

编辑

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