Is it possible to add a table somewhere that all databases in that instance have access to it?
Thanks
You can create "Main" database and access it with SQL queries from any other database in that SQL Server instance. It's just matter of user's privileges. Full syntax for table_or_view_name is database.schema.object_name.
SELECT * FROM mytable AS t
INNER JOIN Main.dbo.SharedTable AS s ON t.Id=s.Id
....
Chack documentation also - FROM (Transact-SQL)
Edit: I added the second solution.
First solution You could try using undocummented system procedure sp_msforeachdb:
EXEC sp_msforeachdb @command1=N'
USE ?;
IF ''?'' NOT IN (''master'',''model'',''tempdb'',''msdb'',''ReportServer'',''ReportServerTempDB'')
BEGIN
CREATE TABLE CocoJamboTable (
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
);
END
';
EXEC sp_msforeachdb @command1=N'
USE ?;
IF ''?'' NOT IN (''master'',''model'',''tempdb'',''msdb'',''ReportServer'',''ReportServerTempDB'')
BEGIN
SELECT DB_NAME() AS DbName, name AS TableName, object_id FROM ?.sys.tables
WHERE name=''CocoJamboTable''
END
';
Second solution You could generate a T-SQL script with all CREATE TABLE statements:
SET NOCOUNT ON;
PRINT 'BEGIN TRAN;'
/*
SELECT
'USE '
+ QUOTENAME(db.name)
+ ';'
+ '
CREATE TABLE CocoJamboTable (
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
);'
+ CHAR(13)
*/
SELECT
CASE
WHEN db.name IN ('master','model','tempdb','msdb','ReportServer','ReportServerTempDB','ASPNETDB')
THEN '-- Explicit skipping: ' + QUOTENAME(db.name)
WHEN db.state <> 0
THEN '-- Implicit skipping (not online): ' + QUOTENAME(db.name)
WHEN db.is_read_only=1
THEN '-- Implicit skipping (read only): ' + QUOTENAME(db.name)
--WHEN other filter
-- THEN '-- Implicit skipping (my filter): ' + QUOTENAME(db.name)
ELSE
'CREATE TABLE ' + QUOTENAME(db.name) + '.dbo.CocoJamboTable (
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
);'
END
+ CHAR(13)
FROM sys.databases db
ORDER BY db.name;
PRINT 'ROLLBACK;'
PRINT '-- COMMIT;'
Then, press Ctrl+T (text results), F5 (execute), F6 (jump to text results panel), Ctrl+A (select all), Ctrl+C (copy), Ctrl+N (new window), Ctrl+V (paste - paste the script generated in previous step), F5 (execute).
Sample T-SQL script:
BEGIN TRAN;
CREATE TABLE [Comisioane].dbo.CocoJamboTable (
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
);
-- Implicit skipping (read only): [Credite]
-- Explicit skipping: [master]
-- Explicit skipping: [model]
-- Explicit skipping: [msdb]
-- Explicit skipping: [ReportServer]
-- Explicit skipping: [ReportServerTempDB]
CREATE TABLE [SIMUR4].dbo.CocoJamboTable (
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
);
ROLLBACK;
-- COMMIT;
sp_msforeachdb. It's undocumented, unsupported, and completely broken (and Microsoft will not fix it). See sqlblog.org/blogs/aaron_bertrand/archive/2010/12/29/… and mssqltips.com/sqlservertip/2201/…sp_msforeachdb. For others problems I use transactions: BEGIN TRANSACTION; ...sp_for... ROLLBACK/COMMIT;. For dbo.ForEachDB_MyWay, will be nice to see a warning when skipping read only/not online databases.