0

Is it possible to add a table somewhere that all databases in that instance have access to it?

Thanks

2 Answers 2

2

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)

Sign up to request clarification or add additional context in comments.

3 Comments

I don't want create linked server.I want a some thing like system views that accessible from all databses
This is not linked server. Linked server means that your "shared" database is on another SQL Server Instance. You don't have to configure anything additionally. Give it a try.
FYI also referencing a table using a linked server requires a 4 part name [LinkedServerName].[DatabaseName].[SchemaName].[TableName]. To reference a table in another database on the same server you only have to use a 3 part name. [DatabaseName].[SchemaName].[TableName]
0

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;

3 Comments

No, please stop recommending 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/…
@AaronBertrand: Thank you for your comment. I use sometimes this stored procedure for adhoc tasks. Yes, it is undocumented, I have written. It skips some databases: yes. This is the reason why I have written the second 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.
@AaronBertrand: I added the second solution. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.