Prepared SQL Studio Expression

Question

I am working on a company that stores each of its customer information in a different database. When the table needs to be changed, I have to go to each database and run the ALTER TABLEscript. Is there a way I can use a prepared statement to run all 100+ DBO names?

ALTER TABLE ?.dbo.profileTable
ADD COLUMN profileStatus int

Where? = 'CompanyA, CompanyB, CompanyC'or something like that?

Answer 1

Use Sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; alter query'

[?] used as a placeholder for a previously undefined database name

You can modify the query to suit your needs to exclude the use of system databases, as shown below.

 EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF DB_ID(''?'') > 4 begin yourquery end'

Answer 2

, , , .

Declare @TableName Varchar(8000) = 'ProfileTable'
Declare @Sql Varchar(8000)

Select @Sql = Stuff(
(Select ';', 'Alter Table ' + Name + SqlText
    From sys.databases
    Cross Apply (Select '.dbo.profileTable ADD profileStatus int' SqlText) CA
    Where Case When State_Desc = 'ONLINE'
            Then Object_Id (QuoteName(Name) + '.[dbo].' + @TableName, 'U')
        End Is Not Null

    FOR XML PATH('')
),1,1,'')

Exec (@Sql)

Answer 3

This ?before is the database ( [database].[schema].[table]). This way you can use sp_MSforeachdbor, as I prefer, use the view sys.databasesto prepare dynamic queries.

Beware, both methods can affect system databases.

Take a look at this solution:

DECLARE @query nvarchar(MAX)='';

SELECT @query = @query + 'USE '+QUOTENAME(name)+';ALTER TABLE dbo.profileTable ADD profileStatus int;'
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name)+'.dbo.profileTable', 'U') IS NOT NULL

EXEC(@query)

It adds a column col1 intto each dbo.profileTable in each database.