如何编写SQL脚本以检查列是否存在于表中（如果不存在）将创建该列 [英] How to write a SQL script to check if the column exists into the table if not exists create the column

问题描述

正在研究GIS数据库，其数量与表格中的挑战相似，但它们的列数不同，例如其他表A有5个表B有3个。

我想要做的是使所有表具有相同数量的列，例如如果表A有5列而表B必须有5列也不是3列，因为所有这些表都有相同的数据。

我想通过编写sql脚本来实现这一点，这样就创建了所有的列不在表中如果列已经是它必须忽略它并添加其他列



我尝试过：

Am working on GIS Database with number of tables that are similar the challenges in this tables they don't have the same number of column e.g. other table A has 5 table B has 3.
what I want to do is to make all the tables have the same number of column e.g. if table A has 5 column and Table B must have 5 columns also not 3 because all those tables have the same data.
I want to achieve this by writing sql script that gone create all the columns that are not in the table if the columns is already the it must ignore it and add other columns

What I have tried:

Below is the script that I tried the challenge with this one I have to insert one column at time, what I want is to insert multiple columns that are not included in the table once.

USE GIS_DOD_Working
Go
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '[dbo].[LO17_BUILDINGS_F000]' AND COLUMN_NAME = 'CAT')
BEGIN

    ALTER TABLE [dbo].[LO17_BUILDINGS_F000] ADD 
       [CAT]  nvarchar (254) NULL 

    

END

推荐答案

正如评论中所提到的，这是一个非常糟糕的数据库设计。



但是如果你只是想生成一个脚本列表来将缺少的列添加到表中，这样的东西应该可以工作：

As mentioned in the comments, this is an extremely bad database design.

But if you just want to generate a list of scripts to add the missing columns to the tables, something like this should work:

WITH cteTables (Name) As
(
    -- List of the tables to modify:
              SELECT N'[dbo].[Table1]'
    UNION ALL SELECT N'[dbo].[Table2]'
    UNION ALL SELECT N'[dbo].[Table3]'
),
cteColumns (Name, Definition) As
(
    -- List of the columns to create:
              SELECT N'Column1', N'nvarchar(254) NULL'
    UNION ALL SELECT N'Column2', N'nvarchar(254) NULL'
),
cteToCreate As
(
    SELECT
        T.Name As TableName,
        C.Name As ColumnName,
        C.Definition
    FROM
        cteTables As T
        CROSS JOIN cteColumns As C
    WHERE
        Not Exists
        (
            SELECT 1
            FROM sys.columns As E
            WHERE E.object_id = OBJECT_ID(T.Name)
            And E.name = C.Name
        )
)
SELECT
    N'ALTER TABLE ' + T.TableName + N' ADD '
    + STUFF(
        (
            SELECT N', ' + QUOTENAME(C.ColumnName) + N' ' + C.Definition 
            FROM cteToCreate As C 
            WHERE C.TableName = T.TableName 
            ORDER BY C.ColumnName 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 
        1, 2, N'')
    + N';'
FROM
    cteToCreate As T
GROUP BY
    TableName
;

如果执行该操作，您将获得执行创建缺失列所需的命令列表。例如：

If you execute that, you'll get a list of commands you need to execute to create the missing columns. For example:

ALTER TABLE [dbo].[Table1] ADD [Column2] nvarchar(254) NULL;
ALTER TABLE [dbo].[Table3] ADD [Column1] nvarchar(254) NULL, [Column2] nvarchar(254) NULL;

这篇关于如何编写SQL脚本以检查列是否存在于表中（如果不存在）将创建该列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！