Showing posts with label schema. Show all posts
Showing posts with label schema. Show all posts

Sunday, 6 July 2008

Object Schema Ownership

Detect which objects are owned by sql logins -
SELECT * FROM database.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_OWNER IN (SELECT NAME FROM MASTER.DBO.SYSLOGINS)
GO
USE database;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
GO

Saturday, 4 August 2007

Moving tables to a new schema


-- Moving tables to a new schema

-- step 1 - create the schema

create schema [migration]

-- step 2 - use sql to build the sql for tables to transfer
select 'alter schema [migration] transfer dbo.' + name + ';' from sys.tables

-- step 3 - run the sql generated by step 2