how to create a data type and make it available in all Databases?
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.
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
.