sql-server - Microsoft SQL Server：在大厅数据库中查找和更新确切值，即使它是 PRIMARY

Question

我想更改未知表或列的特定值。我找到了帮助我搜索的代码。我发现在某些表中，它是主列。

我必须做这份工作，因为我有很多价值观需要改变。

我试图做代码，但我得到了很多错误：

列名“TableNameA”无效。

列名“ColumnNameA”无效。

希望得到帮助，因为我还是 SQL 新手。

我应该怎么办？

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '4523' 
Declare @NewValueInt int = 4195403
Declare @NewValueVarChar nvarchar(20) = '4194523'
 /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
    IF @TableName IS NOT NULL
    BEGIN
        DECLARE @sql VARCHAR(MAX)
        SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
                AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
                AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
                AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
        INSERT INTO @ColumnNameTable
        EXEC (@sql)
        WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
        BEGIN
            PRINT @ColumnName
            SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
            SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
            WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
            ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
            INSERT INTO #Results
            EXEC(@sql)
            IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
            BEGIN
                SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
                    ' FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
                EXEC(@sql)


                DECLARE @sqlU VARCHAR(MAX)
                Declare @sqlN VARCHAR(MAX)
                
                
                SET @sqlU = N'UPDATE ' + quotename(@SearchStrTableName) + ' SET ' +quotename(@SearchStrColumnName)+ ' = ''' +@NewValueVarChar+ ''' WHERE ' +quotename(@SearchStrColumnName)+ ' = ''' + @SearchStrColumnValue + '''';
                EXEC (@sqlU)
                SET @sqlN = N'Update ' + quotename(@SearchStrTableName) + ' SET '+ quotename(@SearchStrColumnName)  +'=''' +@NewValueVarChar+ '''where'+ quotename(@SearchStrColumnName) + '='+ @SearchStrColumnValue
                EXEC (@sqlN)
            END
            DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
        END 
    END
END
SET NOCOUNT OFF

IF OBJECT_ID('tempdb..#Abd_tmptbl') IS NOT NULL DROP TABLE #Abd_tmptbl
CREATE TABLE #Abd_tmptbl (TableNameA nvarchar(128), ColumnNameA nvarchar(128), ColumnValueA nvarchar(max),ColumnTypeA nvarchar(20), Count int)
INSERT INTO #Abd_tmptbl
SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

DECLARE @Tableee VARCHAR(20), @Columnee varchar(20), @Constraint varchar(20)
DECLARE @items TABLE(tabl int, clmn int)
Create Table #PK_tbl (PK_Col varchar (10))
WHILE EXISTS (select TOP 1 TableNameA FROM #Abd_tmptbl)
    BEGIN
    SELECT TOP 1 @Tableee = TableNameA, @Columnee = ColumnNameA
    ------------ GET All PKs of the Table ---------------------
    DECLARE @PK_sql varchar(max) = N'INSERT INTO #PK_tbl(PK_Col)
    (SELECT Col.Column_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
    WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = "PRIMARY KEY" AND Col.Table_Name = ' + @Tableee +')'
    EXEC (@PK_sql)
    ------------------------------------------------------------
    ------------ GET CONTRAINT Name ----------------------------
    SET @Constraint = N'SELECT name FROM sys.key_constraints  WHERE type = "PK" AND OBJECT_NAME(parent_object_id) = ' + @Columnee
    ------------------------------------------------------------
    ------------ RELEASE Table From CONSTRAINTS ----------------
    DECLARE @REL_tbl VARCHAR(max) = N'ALTER TABLE' + @Tableee +'DROP CONSTRAINT'+ @Constraint
    EXEC (@REL_tbl)
    ------------------------------------------------------------
    DECLARE @Update_tbl VARCHAR(max) = N'UPDATE '+ @Tableee + 'SET ' + @Columnee + '=' + @NewValueInt+ ' WHERE' + @Columnee + '=' + @SearchStrColumnValue
    EXEC (@Update_tbl)

    DECLARE @Sealing_tbl VARCHAR(max) = N'ALTER TABLE' + @Tableee + 'ADD CONSTRAINT' + @Constraint + 'PRIMARY KEY CLUSTERED (SELECT * FROM #PK_tbl)'
    EXEC (@Update_tbl)

    END

标签： sql-server