Menu
I need to access the user defined table type from another database.
I have used the following format:
DECLARE @Hierarchy AS [DatabaseName].[Schema].[Table]
I get the following error:
The type name '[DatabaseName].[Schema].[Table]' contains more than the maximum number of prefixes. The maximum is 1
How can I access the User Defined table type from another database?
689
To access the data from table which exists in different databases hosted in same SQL Server instance, you need to provide full qualify name for table such as table name including database name, and schema (database_name. schema_name. table_name) as shown below.
Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's SYSTEM tablespace.
SImple: You can not.
Schema definitions must be from the same database - and a user defined type in your usage scenario is a schema definition.
This is why there is only max. 1 prefix allowed - 1 prefix identifies another schema (within the same database).
So, you will have to copy the type definition over to your other database to use it.
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
