循环遍历数据库中的所有表，并根据 SQL Server 中的列数据类型插入一行

Question

我希望能够遍历数据库中的所有表，并找出每个表的数据类型和长度。 如果数据类型为 int，我必须在该列中插入 0，如果列是字符串并且长度小于 30，则插入"UKN"或 如果列是字符串并且刨丝器大于 30，则插入为"未知"。

如何编写循环遍历所有表并根据条件插入数据库的脚本？对于特定表，如何获取每列和相关数据类型并进行检查？

Answer 1

您可以使用

information_schema.columns系统视图，无需使用可怕的CURSOR

USE <DATABASE_NAME>
GO
SELECT C.TABLE_CATALOG
     , C.TABLE_SCHEMA
     , C.TABLE_NAME
     , C.COLUMN_NAME
     , C.DATA_TYPE
     , C.CHARACTER_MAXIMUM_LENGTH
    , CASE
        WHEN C.DATA_TYPE = 'int' THEN '0' 
        WHEN C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') 
        THEN 
            CASE
                WHEN C.CHARACTER_MAXIMUM_LENGTH <30 then 'UKN' 
                ELSE 'UnKnown' 
            END 
        END 'OUTPUTFROM'
FROM
    information_schema.columns  C
ORDER BY 
    TABLE_NAME

要插入

;WITH cteX
AS(
    SELECT C.TABLE_CATALOG
         , C.TABLE_SCHEMA
         , C.TABLE_NAME
         , C.COLUMN_NAME
         , C.DATA_TYPE
         , C.CHARACTER_MAXIMUM_LENGTH
        , CASE
            WHEN C.DATA_TYPE = 'int' THEN '0' 
            WHEN C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') 
            THEN 
                CASE
                    WHEN C.CHARACTER_MAXIMUM_LENGTH <30 then 'UKN' 
                    ELSE 'UnKnown' 
                END 
            END 'OUTPUTFROM'
    FROM
        information_schema.columns  C
)
INSERT INTO dbo.TABLE 
    ( database_name, table_schema, table_name, column_name, Output_from)
SELECT
    TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, OUTPUTFROM
FROM 
    cteX X

更新

;WITH cteX
AS(
    SELECT C.TABLE_CATALOG
         , C.TABLE_SCHEMA
         , C.TABLE_NAME
         , C.COLUMN_NAME
         , C.DATA_TYPE
         , C.CHARACTER_MAXIMUM_LENGTH
        , CASE
            WHEN C.DATA_TYPE = 'int' THEN '0' 
            WHEN C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') 
            THEN 
                CASE
                    WHEN C.CHARACTER_MAXIMUM_LENGTH <30 then 'UKN' 
                    ELSE 'UnKnown' 
                END 
            END 'OUTPUTFROM'
    FROM
        information_schema.columns  C
)
UPDATE
    T
SET
    T.OUTPUTFROM = X.OUTPUTFROM
FROM 
    dbo.TABLE T
INNER JOIN 
    cteX X ON   X.TABLE_CATALOG = T.database_name 
            AND X.TABLE_SCHEMA = T.table_schema
            AND X.table_name = T.table_name
            AND X.column_name = T.column_name

Answer 2

使用系统表：

select    t.name as TableName
        , c.name as ColumnName
        , ty.name as [DataType]
        , case  when ty.name = 'int' then '0' 
                when ty.name in ('char', 'nchar', 'varchar', 'nvarchar') 
                    then case   when c.max_length <30 then 'UKN' 
                                else 'UnKnown' end 
           end as OUTPUT
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
inner join sys.types ty on ty.user_type_id = c.user_type_id
where t.type = 'U'