[SQL Server] Change Schema Name Of Table In SQL Server

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ?

example:

FROM

dbo.Employees

TO

exe.Employees

I tried with this query.

ALTER SCHEMA exe TRANSFER dbo.Employees

But this give me error.
Error:
Cannot alter the schema 'exe', because it does not exist or you do not have permission.
What I missed or did wrong ?

Solution:

Create Schema :

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe')) 
BEGIN
    EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END

 

ALTER Schema :

ALTER SCHEMA exe 
    TRANSFER dbo.Employees

 

[T-SQL] DELETE, TRUNCATE and RESEED Identity

Create a temp table with Identity column beginning with value 11. The seed value is 11.

USE [TempDB]
GO-- Create TableCREATE TABLE [dbo].[TestTable]([ID] [int] IDENTITY(11,1) NOT NULL,[var] [nchar](10) NULL
) ON [PRIMARY]
GO-- Build sample dataINSERT INTO [TestTable]VALUES ('val')GO

When seed value is 11 the next value which is inserted has the identity column value as 11.

– Select Data

SELECT *FROM [TestTable]
GO


Effect of DELETE statement

-- Delete DataDELETE FROM [TestTable]
GO

When the DELETE statement is executed without WHERE clause it will delete all the rows. However, when a new record is inserted the identity value is increased from 11 to 12. It does not reset but keep on increasing.

-- Build sample dataINSERT INTO [TestTable]VALUES ('val')GO-- Select DataSELECT *FROM [TestTable]


Effect of TRUNCATE statement

-- Truncate tableTRUNCATE TABLE [TestTable]
GO

When the TRUNCATE statement is executed it will remove all the rows. However, when a new record is inserted the identity value is increased from 11 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

-- Build sample dataINSERT INTO [TestTable]VALUES ('val')GO-- Select DataSELECT *FROM [TestTable]
GO

Effect of RESEED statement

If you notice I am using the reseed value as 1. The original seed value when I created table is 11. However, I am reseeding it with value 1.

-- ReseedDBCC CHECKIDENT ('TestTable', RESEED, 1)GO

When we insert the one more value and check the value it will generate the new value as 2. This new value logic is Reseed Value + Interval Value – in this case it will be 1+1 = 2.

-- Build sample dataINSERT INTO [TestTable]VALUES ('val')GO-- Select DataSELECT *FROM [TestTable]
GO

Here is the clean up act.

-- Clean upDROP TABLE [TestTable]
GO

[T-SQL] My favourite SQL shortcuts

Today i would tell you my favourite sql shortcuts and the way to setup.

In SSMS , go to Tools and then Options.

In the tab of Options , you can setup what sql statements you run often.

You can execute them very simply , just highligh the table and press what ever key you have configured.