Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 11 November 2013

How to change Diffrent Schema to DBO?

How to change Diffrent Schema to DBO?

                                                              In our DB there are somany diffrent dbo schemas are available for examble

dbo
db_datareader
db_datawriter
usercreatedschema
sales
..................................etc




but we can change one schema to another by two method one SMO(SqlManagementObject) window and T-SQL way.

SMO way:

Just right click on table and click 'MODIFY'


and now press 'F4' (This facititliy available on only SQL 2005)



Now u can choose properties window under schema tab u can choose wht do u want schema here we should choose 'dbo'

So


now accept yes to proceed furthur changes


and now u can see that Sprockets schema is changed as dbo in table


so now u can query using

select * from dbo.NinePron--------valid

select * from Sprockets.NinePron--------not valid bcoz it changed to dbo


Tsql method:

Right click table and script table as  create new window option

USE [testdb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NineProngs]------here you should change to [dbo].[NineProngs] from
----[Sprockets].[NineProngs]
(
[source] [int] NULL,
[cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]

drop table [Sprockets].[NineProngs]-------drop previous schema with table

u can modify and create it new schema and dropped table previous schema table