HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

how to create a data type and make it available in all Databases?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
availablecreatealldatabasesmaketypehowanddata

Problem

If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link:

Making a Procedure Available in all Databases

that give me this code example:

Just by following the example above, I can call my procedure from any database.

what about if I create a table data type in master,
how can I use it in any of my databases?

use master

        IF NOT EXISTS (select * from sys.types where name = 'theReplicatedTables') 
                CREATE TYPE theReplicatedTables AS TABLE 
                (  OBJ_ID INT NOT NULL,
                  PRIMARY KEY CLUSTERED (OBJ_ID)
                );

use APIA_Repl_Sub
go
declare @the_tables [dbo].[theReplicatedTables]

Solution

There is no way to do that with table types. You will need to replicate that in all databases. But, if you want this table type available while creating a new database, you can just add it in the model database.
There are a couple restrictions to using table-valued parameters. You can't even use table-valued parameters across databases. Check some details here.

Context

StackExchange Database Administrators Q#234144, answer score: 6

Revisions (0)

No revisions yet.