How to TRUNCATE Data From All TABLES of a DataBase in SQL Server

TRUNCATE Data From All TABLES of a DataBase in SQL Server

Here the following script help full to you to truncate data from all tables in a database

USE dbname
go
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DELETE FROM ?'
go
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'
go

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

This is for for disable all the constraints in database

EXEC sp_MSforeachtable 'DELETE FROM ?'

Here we can delete all the data from tables

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT

 Again we need to enable the constraints

Now,

 EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'

By using DBCC command reset the identity

All process comes under truncation of a database 

The purpose of reseed is to re-set the identity value ,because truncate will automatically resets, but delete cannot. So it is mandatory to use the DBCC command for reset Identity value 

Display State Under City in SQL Server

How to Display State Under City in SQL Server

In my DB i have Two tables like State and City. Here State id is referenced

I need to display the data First Metro City and next StateName under that City name in Alphabetical order.

For finding is metro city or not i taken a flag named IsMetrocity

Here is the solution:

select CityName,StateId from City where IsMertoCity=1
union all
SELECT sc.name,sc.StateId FROM  state AS s2
LEFT JOIN (SELECT s.StateNmae AS name, s.stateid,0 as IsMertoCity FROM   state s
            UNION ALL
            SELECT c.cityname AS name, c.StateId,IsMertoCity FROM   city c ) AS sc
ON sc.stateid = s2.stateid

Here first 4 are the metroCities and Starting StateId 1 is StateName and remaining Cities,next starting stateid 2 is StateName remaining are Cities under the State and so on.

Job Scheduling in SQL SERVER

JOB SCHEDULING

Step 1:  Go to SQL Server Agent-->Click on jobs-->NEW JOB

Step 2: Provide the name and Database owner
At the description you can write like it could give brief introduction about the job

Step 3: Select the steps-->Click on new

Step 4: Provide the step name
Type is a T-SQL (from drop down list you can choose different types)
Database is your database Name
Write the command what we have to schedule daily
(My self here i am taking the backup of one of my data bases) Click OK

Below snapshot show after the Step finished

Step 5: For scheduling click NEW

Step 6: Give the suitable name
Schedule type, Here from the drop down we have different types, According to type of schedule select one

Click OK

Find your JOB name here(If job created successfully)

In the error logs you can find the summery of your job

Detach and Attach in SQL SERVER

Detach and Attach

Step 1: Attach and Detach database
Before proceeding need to know what is attach and detach!
While we took backup of a database, the .bak file saved in drive and also exists in server
when ever use a detach option this will takes backup of database and drops the database from server
While attach like a restore of a database which already backedup.

The following are the steps how we can attach and detach the database

Step 2: Click on database -->Tasks-->Detach

If you found following error, Set your database to Single User mode using SET operator

Now choose drop connections then click OK

Also the following is the SP also how to set database into single user mode

Attach:
Click on database-->Attach

Choose database which you want to restore

click ok

The database came to previous position

SQL SERVER 2014 Installation

SQL SERVER 2014 Installation

Step 1: Click on your set up file 

Step 2: Click on installation then select first one ( New SQL Server standard-alone installation or .... )

Step 3: Specify a free edition (it could be free for 180 days)
If you have a product key then choose enter product key

Step 4: Check the BOX accept the licence terms then click next

Step 5: After finished the Updates Checking click next

Step 6:If no errors you can click next

Step 7: Choose one from the following based on which type you need. All about headings given the features in description

Step 8: Select instance features and/or Shared features according to your work requirement click next

Step 9: Give the instance name may be default or Named

Step 10:  Choose the Service account Name and startup Type

Step 11: Server  configuration -->Authentication Mode
Here Mixed mode we can keep security for log-in. Windows authentication Mode required no security but another must--> Specify SQL Server administrator -->NEXT

and this is for Analysis Services

Step 12: This is about the reporting services choose install only or install and configure

This could be the final step

Import Data from EXCEL

How to Import Data from EXCEL to Destination

Import data from excel through wizard 

Step 1: Choose your database where to import data -->Tasks-->Import Data

Step 2: Click Next

Step 3: Choose the Data source as a Microsoft Excel and browse the file path as well as choose the Excel Version click next

Here we have the options to get the columns Dynamically (like First row has column names) or Manually we have to provide

Step 4:Now choose our destination and Authentication type also database -->click Next

Step 5: Choose either option click next

Step 6: Select the sheet where you have a required data then click next

Step 7:Here we have the options to choose either Package or run immediately 

Run immediately is a process which retrieves the data instantly 

If it is a package we can run package along with configuration file at any environment 

Step 8: Close 

Backup, Restore, and Move the SSIS Catalog

Backup, Restore, and Move the SSIS Catalog

For full details go through the web page

http://technet.microsoft.com/en-us/library/hh213291.aspx

Cannot drop database [SSISDB] because it is currently in use.

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: --/--/20-- 12:53:55 PM ******/

alter database [SSISDB] set single_user with rollback immediate


 drop database [SSISDB]

Restore database [SSISDB] 

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: 3/10/2014 12:53:46 PM ******/
CREATE DATABASE [SSISDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SSISDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SSISDB] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_NULLS OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_PADDING OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [SSISDB] SET ARITHABORT OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [SSISDB] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [SSISDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [SSISDB] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [SSISDB] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [SSISDB] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [SSISDB] SET  DISABLE_BROKER
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [SSISDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [SSISDB] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [SSISDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [SSISDB] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [SSISDB] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [SSISDB] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [SSISDB] SET RECOVERY FULL
GO

ALTER DATABASE [SSISDB] SET  MULTI_USER
GO

ALTER DATABASE [SSISDB] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [SSISDB] SET DB_CHAINING OFF
GO

ALTER DATABASE [SSISDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [SSISDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [SSISDB] SET  READ_WRITE
GO