Selecting the same field from multiple tables

Question

I'm writing a query to copy databases for Dynamics NAV to make test environments. The difficulty that I'm addressing is that once the new test environment is made, I need to clear out information from some of the fields so that live data doesn't get processed by the test environment.

The way that NAV sets up the tables in the database is using the format of [Company_Name$Table_Name].[Field] and in more recent versions, it adds a numerical sequence at the end of the table name.

I'm trying to add a section to my script that will dynamically go through the tables to find all instances of the Table Name and delete the data in a specified field on that table. The table name itself is constant and the fields in each of the tables with that table name are constant.

What I've tried doing so far as a test to find the data is a nested select statement to get a list of the records across those tables. I can get the table names using:

select *
from sys.tables
where name like '%table_name%

What I don't know/understand is how to pass that information in to the subquery of:

Select [Profile]from [table_name] where [field] is not null

What I tried to do was the following to just get the data in general, but when I do it says the syntax is incorrect at the end and I'm not sure :

Select *
From(select [name]
    From sys.tables
    where name like '%tableName%'
)

The last thing I tried was this in an attempt to put the table name in front of the field name but that didn't work either

select (select [name] 
    from sys.tables 
    where name like '%Interface Profile').[Profile] 
from (
    select [name] 
    from sys.tables 
    where name like '%Interface Profile') as profiles

============================================================================== Update

Based on @siggermannen 's suggestion I came up with the following code

DECLARE @companylist TABLE (name_like nvarchar(128),field SYSNAME, field_value_to_set nvarchar(max))
INSERT INTO @companylist (
                name_like, 
                field,
                field_value_to_set)
                Values ('%Interface Profile%','Path',null)
                ,   ('%Interface Profile%','Archive Path',null)
                ,   ('%Interface Profile%','Import Error Path',null)
                ,   ('%PW Setup%','Communication PDF Path',null)
                ,   ('%PW Trx Activity%','Document Path',null)
                ,   ('%TPL Document Index Import%','Journal Importpath',null)
                ,   ('%TPL Document Index Import%','Exportpath',null)
                ,   ('%E_D_I_ Template%','Interface File Path',null)
                ,   ('%E_D_I_ Setup%','Common Receive Path',null)
                ,   ('%E_D_I_ Setup%','Common Work Path',null)
                ,   ('%PW Communication Rule%','To Email Address',null)
                ,   ('%PW Communication Rule%','CC Email Address',null)
                ,   ('%PW Communication Rule%','BCC Email Address',null)

DECLARE @SQL NVARCHAR(MAX)
,   @name SYSNAME
,   @field SYSNAME, @field_value_to_set NVARCHAR(MAX)
,   @start INT
,   @end INT

DECLARE CR_X CURSOR READ_ONLY FORWARD_ONLY LOCAL STATIC FOR
    SELECT  t.name
    ,   field
    ,   field_value_to_set
    ,   ROW_NUMBER() OVER(PARTITION BY name_like ORDER BY id) AS sort
    ,   ROW_NUMBER() OVER(PARTITION BY name_like ORDER BY id DESC) AS sort_last
    FROM    @companylist fc
    INNER JOIN sys.tables t
        ON  t.name COLLATE DATABASE_DEFAULT LIKE fc.name_like COLLATE DATABASE_DEFAULT
    INNER JOIN sys.columns sc
        ON  sc.object_id = t.object_id
        AND sc.name COLLATE DATABASE_DEFAULT = fc.field COLLATE DATABASE_DEFAULT
    WHERE   t.is_ms_shipped = 0 -- just in case
    ORDER BY t.name

OPEN CR_X
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CR_X INTO @name, @field, @field_value_to_set,@start, @end

    IF @@FETCH_STATUS <> 0
        BREAK
    -- Build SQL update string
    IF @start = 1
        SET @SQL = 'UPDATE ' + QUOTENAME(@name) + N'
     SET '
     if @start > 1
      set @SQL = @SQL + ','

      set @SQL = @SQL + QUOTENAME(@field) + ' = ' + @field_value_to_set

    IF @end = 1 -- Energize!
    BEGIN
        PRINT @SQL
        EXEC(@SQL)
    END

END
CLOSE CR_X
DEALLOCATE CR_X

select [Communication PDF Path]
FROM [CRONUS 3PL DEMO 110$PW Setup]

--select *
--from anothertable
--Clean up sample data
--drop table tableName
--drop table anothertable

Where I'm currently stuck is that I'm getting the following errors when I run it, but I'm not sure how to figure out what's in t

Msg 207, Level 16, State 1, Line 31
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'id'.

Completion time: 2023-06-28T10:25:38.7999707-04:00

Any thoughts?

If I am understanding correctly, you'll need to **safely** build dynamic SQL. I often (but not always) find that requirements such as this are due to a design flaw. Considering you have tables with the name `[Company_Name$Table_Name].[Field]` certainly sounds like you do have flaw in your design here; you should have 1 table with a `CompanyName` (or more likely `CompanyID`) column. I would suggest that fixing your design should be your priority here. — Thom A, Jun 21 '23 at 18:40
You need to generate and execute *dynamic SQL*. If you search for that term, you should find loads of examples. However, if this is a manual process that you perform infrequently, a "poor man's" solution may be simpler. Just write a select that generates the needed commands, and then paste, review, and execute them in a new window. Something like `SELECT 'SELECT * FROM ' + QUOTENAME(t.name) AS sql from sys.tables t where t.name like '%Interface Profile'`. Customize to your needs. — T N, Jun 21 '23 at 19:04

score 0 · Answer 1 · answered Jun 21 '23 at 20:06

Here's a little generic solution you might be able to use:


-- Create some sample data
create table tableName (field1 INT, field2 INT)

insert into tableName values (1, 2), (3,4)

create table anothertable(field1 INT, field2 varchar(30), field3 NVARCHAR(300))

insert into anothertable (field1, field2, field3)
select 1, 'test', 'another test'


declare @fields_to_clear table (
    name_like NVARCHAR(100)
,   field SYSNAME
,   field_value_to_set NVARCHAR(MAX)
,   id int identity
)

-- Here are the names and fields you want cleared
INSERT INTO @fields_to_clear (
    name_like, field, field_value_to_set
)
VALUES  ('%tableName%', 'field1', 'NULL')
,   ('%anotherTable%', 'field2', 'NULL')
,   ('%anotherTable%', 'field3', 'NULL')

DECLARE @SQL NVARCHAR(MAX)
,   @name SYSNAME
,   @field SYSNAME, @field_value_to_set NVARCHAR(MAX)
,   @start INT
,   @end INT

-- Get all matching tables and fields
DECLARE CR_X CURSOR READ_ONLY FORWARD_ONLY LOCAL STATIC FOR
    SELECT  t.name
    ,   field
    ,   field_value_to_set
    ,   ROW_NUMBER() OVER(PARTITION BY name_like ORDER BY id) AS sort
    ,   ROW_NUMBER() OVER(PARTITION BY name_like ORDER BY id DESC) AS sort_last
    FROM    @fields_to_clear fc
    INNER JOIN sys.tables t
        ON  t.name COLLATE DATABASE_DEFAULT LIKE fc.name_like COLLATE DATABASE_DEFAULT
    INNER JOIN sys.columns sc
        ON  sc.object_id = t.object_id
        AND sc.name COLLATE DATABASE_DEFAULT = fc.field COLLATE DATABASE_DEFAULT
    WHERE   t.is_ms_shipped = 0 -- just in case
    ORDER BY t.name, sort

OPEN CR_X
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CR_X INTO @name, @field, @field_value_to_set,@start, @end

    IF @@FETCH_STATUS <> 0
        BREAK
    -- Build SQL update string
    IF @start = 1
        SET @SQL = 'UPDATE ' + QUOTENAME(@name) + N'
     SET '
     if @start > 1
      set @SQL = @SQL + ','

      set @SQL = @SQL + QUOTENAME(@field) + ' = ' + @field_value_to_set

    IF @end = 1 -- Energize!
    BEGIN
        PRINT @SQL
        EXEC(@SQL)
    END

END
CLOSE CR_X
DEALLOCATE CR_X

select *
from tableName

select *
from anothertable
--Clean up sample data
--drop table tableName
--drop table anothertable

I create a little foundation table @fields_to_clear, which you can populate with table names as LIKE and name of fields you want to clear, and finally the value you want to set. I use NULLs, but in theory, you can also set them to 0 or whatever else. Just beware that strings might require extra quotes due to the nature of dynamic SQL.

Then i create a cursor which finds matching tables in the system catalog sys.tables and loops fields one by one. The loop builds the dynamic SQL which sets each field to the value in the field_value_to_set column.

You can comment the EXEC(@SQL) just to see the actual SQL being generated.

Be careful with the like-string so you don't blow away your whole database

score -1 · Answer 2 · answered Jun 21 '23 at 19:25

-1

You have to alias inline views in sql server. For example this works

Select *
From(select [name]
    From sys.tables
    where name like '%tableName%'
) as x

answered Jun 21 '23 at 19:25

Bill Smith

1
1

That's not an answer to the primary question of how to manipulate (run DML against) the match tables. – T N Jun 21 '23 at 19:33

Selecting the same field from multiple tables

2 Answers2