Category Archives: MS Sql Server

Restore SQL Server database and overwrite existing database using WITH REPLACE

The RESTORE … WITH REPLACE option allows you to overwrite an existing database when doing a restore. Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database “AdventureWorks” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. Msg 3013, [...]

Tags: , , , ,

Delete All Objects of a Database Using SQL Script

Below is the script which can be used to delete all the database objects – views, functions, procedures, constraints & tables using a this script. /* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = ‘DROP PROCEDURE [dbo].[' + RTRIM(@name) +']‘ EXEC (@SQL) PRINT ‘Dropped Procedure: ‘ + [...]

Tags: ,

Adding or Modifying a Column of a Table in SQL Server Using Command

You can add or modify columns in sql server using simple queries given below: Adding a Column to a table: ALTER TABLE Table_Name ADD NewColumnName CHAR(50) DEFAULT ” NOT NULL MODIFYING A COLUMN IN A TABLE: ALTER TABLE Table_Name ALTER COLUMN Table_Column_Name VARCHAR(50) NULL Below are the points which you need to consider before altering table column: Reducing length of column for eg. changing length of column from varchar(50) to varchar(20) will lead to data loss ie. data will be [...]

Tags: , , , ,

Create database backup using SQL query in SQL SERVER

Taking database backup is a day to day operation which database administrators/developers perform. Below is a script for taking database backup in sql server. DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName VARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘C:\DB_Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name IN (‘DATABASE_NAME’) OPEN db_cursor FETCH NEXT FROM db_cursor INTO [...]

Tags: , ,

Setting Identity Insert On/Off on Table – Sql Server

This command is from SQL Server. This command is designed to enable users to set their own value for identity column in case they want. This is very useful for us. We use it in MERGE and RDA if we insert the client to send to the server where we want to make sure to insert row on the server with the IDENTITY value generated by the client. At any time, only one table in a session can have the [...]

Tags: , ,

Using variable in order by clause in MS SQL Server

Clearly, SQL Server doesn’t like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right? DECLARE @col VARCHAR(9) SET @col = ‘FirstName’ EXEC(‘SELECT * FROM Table_Name ORDER BY ‘ + @col) But dynamic SQL is not a very good solution. So below is another way to use variable in  ORDER BY clause: DECLARE @col VARCHAR(9) SET @col = ‘FirstName’ IF @col = ‘FirstName’ SELECT * FROM Table_Name ORDER BY FirstName IF @col = ‘Email’ SELECT * [...]

How to remove duplicate/repetitive rows from MS SQL Server table?

Below is the script to remove duplicate rows from MS Sql Server table: If you have a primary key in the table (let’s call it Id), you can do something like this with a sub-query: DELETE FROM Table_Name WHERE Id NOT IN ( SELECT MIN(Id) FROM Table_Name b WHERE Table_Name.column1 = b.column1 AND Table_Name.column2 = b.column2 — AND … ) Another way is to use GROUP BY clause within sub query: DELETE FROM Table_Name WHERE Id NOT IN (SELECT MAX(Id) [...]

How to rename a table in MS SQL Server using query?

We can change table name using sp_rename of MS SQL Server. Below is the syntax of command used to rename a table: sp_RENAME ‘Old_Table_Name’, ‘New_Table_Name’ The above statement will rename Old_Table_Name to New_Table_Name.   Tweet