change/alter table schema 2

[sparkbyte]

in SQL2005 how to you change a table's schema?


say table employee was originally created as thus.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployee](
	[EmployeePK] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](15) NOT NULL,
	[EmployeeFN] [varchar](25) NULL,
	[EmployeeMI] [varchar](25) NULL,
	[EmployeeLN] [varchar](25) NULL,
	[EmployeeGen] [varchar](10) NULL,
	[EmployeeDept] [nvarchar](50) NULL,
	[EmployeeDateAdded] [datetime] NOT NULL CONSTRAINT [DF_tblEmployee_EmployeeDateAdded]  DEFAULT (getdate()),
	[EmployeeDateDeleted] [datetime] NULL,
	[EmployeeRemovedBy] [varchar](15) NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[EmployeePK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and shows up as dbo.tblEmployee

What I want to do is begin to alter the database schema (in a developement environment) so that dbo.tblEmployee would be common.tblEmployee and so forth to see how it will effect the UI and hopefully allow better overall security.

Thanks

John Fuhrman

 

[markros]

Check ALTER SCHEMA topic in BOL.

PluralSight Learning Library

 

[sparkbyte]

Create Schema common AUTHORIZATION dbo
go
alter schema dbo Transfer common.tblTrackingTable


New schema creted OK but the tblTrackingTable did not change to the newly created schema.

Thanks

John Fuhrman

 

[sparkbyte]

Sorry the erro is below.

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'tblTrackingTable', because it does not exist or you do not have permission.


it should not be permissions as I am dbo.



Thanks

John Fuhrman

 

[fredericofonseca]

try
alter schema common Transfer dbo.tblTrackingTable

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[markros]

You need to do it the opposite way. See the example

USE AdventureWorks2008R2;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

So,

try

alter schema Common Transfer dbo.tblTrackingTable

PluralSight Learning Library

 

[sparkbyte]

I see says the blind man.........

gave each a star since both answers were correct.

Thanks

Thanks

John Fuhrman