SQL Server Inserting machine name string in SQL query

h9a6wy2h 于 11个月前发布在 Mac

关注(0)|答案(1)|浏览(85)

I have a few machine running databases and the database tables names have the machine name included in them.

Say machine1, machine2, machineN are the machine names. The database names tables are like <machineName>_cm.dbo.TableName and an example would be machine1_cm.dbo.cmobjects .

How can I insert a variable that would pickup the machine name and append the _cm.dbo.Tableme to it as part of a SQL query that I need to run on all these machines?

SELECT columnName1, columnName2, columnName3 
FROM machine1_cm.dbo.DMOBJNAMES ...

I was thinking something like the following would work but it's not:

SELECT columnName1, columnName2, columnName3 
FROM @@servername+_cm.dbo.MYOBJNAMES;

SELECT columnName1, columnName2, columnName3 
FROM SERVERPROPERTY('MachineName' _cm..dbo.MYOBJNAMES;

sql-server

来源：https://stackoverflow.com/questions/76805117/inserting-machine-name-string-in-sql-query

1条答案

按热度按时间

von4xj4u1#

You can use dynamic SQL.

DECLARE @sql nvarchar(max) = '
SELECT columnName1, columnName2, columnName3 
FROM ' + QUOTENAME(CAST(SERVERPROPERTY('MachineName') AS sysname) + '_cm') + '.dbo.MYOBJNAMES;
';

EXEC sp_executesql @sql;

Don't forget to parameterize where possible, you can pass parameters to sp_executesql

Alternatively you can execute the whole batch in the context of that database

DECLARE @proc nvarchar(1000) =
    QUOTENAME(CAST(SERVERPROPERTY('MachineName') AS sysname) + '_cm')
    + '.sys.sp_executesql';

DECLARE @sql nvarchar(max) = '
SELECT columnName1, columnName2, columnName3 
FROM dbo.MYOBJNAMES;
';

EXEC @proc @sql;

赞(0）回复(0）举报 11个月前

我来回答

SQL Server Inserting machine name string in SQL query

1条答案

相关问题

热门标签

最新问答