I’ve been doing a bit database re-structuring lately, and I came across a nice and quick way to move database objects between schemas. Instead of dropping and recreating the object in the schema you require it to be in – simply execute the below:
ALTER SCHEMA MySchema TRANSFER dbo.Sproc;
The above example will move the object “dbo.Sproc” into the “MySchema” schema.
Short and sweet.
A couple of months ago I did a blog post on Finding a list of user defined tables within a SQL Database. Well, today I’ve improved upon that example to include the schema that the table belongs too as well.
USE database
SELECT S.name + '.' + O.name [SchemaTable]
FROM sys.all_objects O JOIN
sys.schemas S ON S.schema_id = O.schema_id
WHERE type = 'U' AND
is_ms_shipped = 0 AND
S.schema_id IN (5, 6, 7, 8, 9)
ORDER BY O.name
The reason I needed a list of tables and the schemas they belong to in this format was because I needed to add two columns to each of them.
I’m far to lazy to open up each of these tables (there were 50 in total) and add the two columns manually, so I used the above SQL, as well as some concatenation to write the query for me:
USE database
SELECT 'ALTER TABLE ' + S.name + '.' + O.name + ' ADD Child VARCHAR(100) NULL,
GrandChild VARCHAR(100) NULL' [SQL]
FROM sys.all_objects O JOIN
sys.schemas S ON S.schema_id = O.schema_id
WHERE type = 'U' AND
is_ms_shipped = 0 AND
S.schema_id IN (5, 6, 7, 8, 9)
ORDER BY O.name
I was then able to copy the results into a new query window and execute, saving myself a lot of time.