Tuesday, 7 April 2015
Web Config - Connection String
connectionString="MultipleActiveResultSets=true;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=ark;Data Source=PCNAME\SQLEXPRESS"
Monday, 6 April 2015
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
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
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
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
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
SQL SERVER - How to delete all tables in database
Remove all Tables
Go
-- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
SQL SERVER – Busiest Database in SQl SERVER using Query
DMV sys.dm_exec_query_stats contained
columns total_logical_reads, total_logical_writes, sql_handle. Column
sql_handle can help to to determine the original query by CROSS JOINing
DMF sys.dm_exec_sql_text. From DMF sys.dm_exec_sql_text Database ID and
from Database ID can be figured out very quickly.
SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)
