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.