how to create a data type and make it available in all Databases?How to restrict sequence like ID type to C...

Re-submission of rejected manuscript without informing co-authors

Add an angle to a sphere

What is it called when one voice type sings a 'solo'?

What is GPS' 19 year rollover and does it present a cybersecurity issue?

"My colleague's body is amazing"

How to deal with fear of taking dependencies

When blogging recipes, how can I support both readers who want the narrative/journey and ones who want the printer-friendly recipe?

Could a US political party gain complete control over the government by removing checks & balances?

What is the command to reset a PC without deleting any files

How is it possible for user's password to be changed after storage was encrypted? (on OS X, Android)

Why did the Germans forbid the possession of pet pigeons in Rostov-on-Don in 1941?

Extreme, but not acceptable situation and I can't start the work tomorrow morning

Does bootstrapped regression allow for inference?

Manga about a female worker who got dragged into another world together with this high school girl and she was just told she's not needed anymore

extract characters between two commas?

Doomsday-clock for my fantasy planet

Could Giant Ground Sloths have been a good pack animal for the ancient Mayans?

How do I create uniquely male characters?

Shall I use personal or official e-mail account when registering to external websites for work purpose?

Why doesn't a const reference extend the life of a temporary object passed via a function?

What happens when a metallic dragon and a chromatic dragon mate?

How to move the player while also allowing forces to affect it

Is there a way to make member function NOT callable from constructor?

Can the Produce Flame cantrip be used to grapple, or as an unarmed strike, in the right circumstances?



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


How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?Stored Procedure - MySQLUsing CREATE TYPE within Stored Procedure Calldeteriorating stored procedure running timesSQL Server CREATE and DROP TABLE Permissions in Stored Procedure OnlyOla Hallengren backup failedMaximizing Availability on a Table When Changing Data TypeInvestigating errors from strange queryExecute DDL statements from a query in stored procedureSynchronisation of multiple databases with GUID type






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







6















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:



enter image description here



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]


enter image description here










share|improve this question































    6















    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:



    enter image description here



    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]


    enter image description here










    share|improve this question



























      6












      6








      6








      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:



      enter image description here



      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]


      enter image description here










      share|improve this question
















      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:



      enter image description here



      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]


      enter image description here







      sql-server sql-server-2016 stored-procedures datatypes table-variable






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited yesterday







      marcello miorelli

















      asked yesterday









      marcello miorellimarcello miorelli

      6,0722163146




      6,0722163146






















          2 Answers
          2






          active

          oldest

          votes


















          5














          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.






          share|improve this answer































            5














            Types don't have the ability to cross database thresholds or to be marked as system. So you'll need to create that type in each database where you want to use it. (I'm not a big fan on relying on things like data or types in master, because they disappear when you move to a different system.)



            For existing databases, you can just create the type in a loop. For new databases (through CREATE DATABASE), create the type in model.






            share|improve this answer
























              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "182"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234144%2fhow-to-create-a-data-type-and-make-it-available-in-all-databases%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              5














              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.






              share|improve this answer




























                5














                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.






                share|improve this answer


























                  5












                  5








                  5







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered yesterday









                  DanDan

                  847518




                  847518

























                      5














                      Types don't have the ability to cross database thresholds or to be marked as system. So you'll need to create that type in each database where you want to use it. (I'm not a big fan on relying on things like data or types in master, because they disappear when you move to a different system.)



                      For existing databases, you can just create the type in a loop. For new databases (through CREATE DATABASE), create the type in model.






                      share|improve this answer




























                        5














                        Types don't have the ability to cross database thresholds or to be marked as system. So you'll need to create that type in each database where you want to use it. (I'm not a big fan on relying on things like data or types in master, because they disappear when you move to a different system.)



                        For existing databases, you can just create the type in a loop. For new databases (through CREATE DATABASE), create the type in model.






                        share|improve this answer


























                          5












                          5








                          5







                          Types don't have the ability to cross database thresholds or to be marked as system. So you'll need to create that type in each database where you want to use it. (I'm not a big fan on relying on things like data or types in master, because they disappear when you move to a different system.)



                          For existing databases, you can just create the type in a loop. For new databases (through CREATE DATABASE), create the type in model.






                          share|improve this answer













                          Types don't have the ability to cross database thresholds or to be marked as system. So you'll need to create that type in each database where you want to use it. (I'm not a big fan on relying on things like data or types in master, because they disappear when you move to a different system.)



                          For existing databases, you can just create the type in a loop. For new databases (through CREATE DATABASE), create the type in model.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered yesterday









                          Aaron BertrandAaron Bertrand

                          154k18298493




                          154k18298493






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Database Administrators Stack Exchange!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234144%2fhow-to-create-a-data-type-and-make-it-available-in-all-databases%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              El tren de la libertad Índice Antecedentes "Porque yo decido" Desarrollo de la...

                              Puerta de Hutt Referencias Enlaces externos Menú de navegación15°58′00″S 5°42′00″O /...

                              Castillo d'Acher Características Menú de navegación