How to Create a SQL Table Under a Different Schema

How do I create a SQL table under a different schema?

1. Right-click on the tables node and choose New Table...
2. With the table designer open, open the properties window (view -> Properties Window).
3. You can change the schema that the table will be made in by choosing a schema in the properties window.

How to create table in another schema SSMS

When you are in the table designer, open the Properties window either from View menu and then Properties Window or just press F4)

In that dialog, you can change various properties of the table, including the schema.

How do I move a table into a schema in T-SQL

ALTER SCHEMA TargetSchema 
    TRANSFER SourceSchema.TableName;

If you want to move all tables into a new schema, you can use the undocumented (and to be deprecated at some point, but unlikely!) sp_MSforeachtable stored procedure:

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"

Ref.: ALTER SCHEMA

SQL 2008: How do I change db schema to dbo

Create an Alias to a user defined table type in a different schema

Quote from comment on the original question:

Alias data types are for scalar data types, you can't create an alias data type of a table type. You'll need to give explicit access to the types on the schema here so that the USER's can declare a parameter of the type. – 

User: Larnu

He gave me a couple of keywords which helped with my google search.

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-type-permissions-transact-sql?view=sql-server-ver15

It is still weird though, as I had allow execution and after giving them rights on the specific stored procedure, I could then remove the rights and it works. Trying to remove the rights first results in the execution failing.

---

Related Topics