Copy table data from database to another database table in same sql server -SQL SERVER

use[test]



GO

SET IDENTITY_INSERT TBL_STATEMASTER ON

INSERT INTO TBL_STATEMASTER (id,stateid,statename,remarks,statestatus,EnteredDate,EnteredBy,UpdatedDate,UpdatedBy)
SELECT id, stateid,statename,remarks,statestatus,EnteredDate,EnteredBy,UpdatedDate,UpdatedBy
FROM ark.dbo.TBL_STATEMASTER

SET IDENTITY_INSERT TBL_STATEMASTER OFF
GO

Results

(2 row(s) affected)

Read More

SQL SERVER - Truncate all table in database i n single query

 use[DBNAME]

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 

Read More

Web Config - Connection String

 
    
   
  
   
     
   

 connectionString="MultipleActiveResultSets=true;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=ark;Data Source=PCNAME\SQLEXPRESS"

 

Read More

SQL SERVER - How to delete all triggers in database

Remove all Triggers
Go
    -- drop all user defined triggers
    Declare @trgName varchar(500)
    Declare cur Cursor For Select [name] From sys.objects where type = 'tr'
    Open cur
    Fetch Next From cur Into @trgName
    While @@fetch_status = 0
    Begin
    Exec('drop trigger ' + @trgName)
    Fetch Next From cur Into @trgName
    End
    Close cur
    Deallocate cur 

Read More

SQL SERVER- How to delete all viwes in Database

Remove all Views
Go
    -- drop all user defined views
    Declare @viewName varchar(500)
    Declare cur Cursor For Select [name] From sys.objects where type = 'v'
    Open cur
    Fetch Next From cur Into @viewName
    While @@fetch_status = 0
    Begin
    Exec('drop view ' + @viewName)
    Fetch Next From cur Into @viewName
    End
    Close cur
    Deallocate cur 

Read More

SQL SERVER - Delete all Stored Procedure in database

Remove all User-defined Stored Procedures

go
 -- drop all user defined stored procedures
    Declare @procName varchar(500)
    Declare cur Cursor For Select [name] From sys.objects where type = 'p'
    Open cur
    Fetch Next From cur Into @procName
    While @@fetch_status = 0
    Begin
    Exec('drop procedure ' + @procName)
    Fetch Next From cur Into @procName
    End
    Close cur
    Deallocate cur 

Read More

SQL SERVER - How to delete all tables in database

Remove all Tables
 Go
    -- drop all user defined tables
    EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 

Read More