Invalid object name "Table_2"

Question

Invalid object name "Table_2"

I am trying to replace two charachters in sql when I use this script on a multi schema database I got this error:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'PayPersonAccounts'. Msg 208, Level 16, State 1, Line 1 Invalid object name "PayPersonAccounts". Msg 208, Level 16, State 1, Line 1 Invalid object name "CMRDirectDeliveryPartHeader"

USE Test
DECLARE @Table NVARCHAR(MAX),@Column NVARCHAR(MAX)
DECLARE Table_Cursor CURSOR
FOR

SELECT T.name, /* Table */
       C.name  /* Column */
FROM sysobjects T,syscolumns C
WHERE  T.id = C.id  AND T.xtype = 'u' /* User Table */
                    AND (C.xtype = 99 /* ntext */
                    OR C.xtype = 35   /* text */
                    OR C.xtype = 231  /* nvarchar */
                    OR C.xtype = 167  /* varchar */
                    OR C.xtype = 175  /* char */
                    OR C.xtype = 239  /* nchar */)

OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Table,@Column
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC ('Update [' + @Table + '] Set [' + @Column + '] = REPLACE(REPLACE(CAST([' + @Column +  '] as nvarchar(max)), NCHAR(1610), NCHAR(1740)), NCHAR(1603), NCHAR(1705))')
FETCH NEXT FROM Table_Cursor INTO @Table,@Column
END CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

+3

sql sql-server tsql

Elham azadfar 03 Apr 17 at 12:58

source to share

2 answers

You need to use the fully qualified table name with the schema. And just to be sure to insert tabe names. Also use sys.tables for tables: sys.tables

QUOTENAME (SCHEMA_NAME (t.schema_id)) + '.' + QUOTENAME (T.name)

+1

Matias Sincovich 03 Apr 17 at 13:14

source to share

Kannan Kandasamy · Accepted Answer · 2017-04-03T13:16:13+0000

I think your schemaname is missing. Trying to check this modified request

USE Test
DECLARE @SchemaName SYSNAME, @Table NVARCHAR(MAX),@Column NVARCHAR(MAX)
DECLARE Table_Cursor CURSOR
FOR

SELECT s.Name  --schemaname
    , T.name, /* Table */
      C.name  /* Column */
FROM sysobjects T inner join 
syscolumns C on
T.id = C.Id
inner join sys.schemas s
on T.uid = s.schema_id
where T.xtype = 'u' /* User Table */
                    AND (C.xtype = 99 /* ntext */
                    OR C.xtype = 35   /* text */
                    OR C.xtype = 231  /* nvarchar */
                    OR C.xtype = 167  /* varchar */
                    OR C.xtype = 175  /* char */
                    OR C.xtype = 239  /* nchar */)

OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @SchemaName, @Table, @Column
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC ('Update [' + @SchemaName + '].[' + @Table + '] Set [' + @Column + '] = REPLACE(REPLACE(CAST([' + @Column +  '] as nvarchar(max)), NCHAR(1610), NCHAR(1740)), NCHAR(1603), NCHAR(1705))')
FETCH NEXT FROM Table_Cursor INTO @SchemaName, @Table,@Column
END CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Invalid object name "Table_2"

More articles: