Tip: MSSQL Drop all triggers

Its funny that sometimes we need to drop all triggers because we inputed a logic that will prohibit a particular code. In cases like this we can drop them all and later create them again. In database with plent of tables, 100 tables, its hard if we drop them one by one. So here is a script that can help you as it just did to me.

SELECT N’DROP TRIGGER ‘ +QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N’.’ +   QUOTENAME(t.name) + N’; ‘ + NCHAR(13)

FROM sys.triggers AS t

WHERE t.is_ms_shipped = 0 AND t.parent_class_desc = N’OBJECT_OR_COLUMN’;


God Bless!

Tip: MSSQL list all SPs by its modified date

In some cases we may want to get from MSSQL list all SPs by its modified date so that we know what we or our college did modified last. Here is a query you may use

SELECT name, OBJECT_DEFINITION(object_id), modify_date
FROM sys.procedures
ORDER BY modify_date DESC

This query will return the SPs from the latest modified stored procedure. Its handy specially when your migrating SPs manually.

God Bless!

MSSQL: Search all Records of a Database

There are times that we need to find something or search all records particular on an unfamiliar database in MSSQL.

Hi,

There are times that we need to find something or search all records particular on an unfamiliar database in MSSQL. It is good to know how to query every item as a worst case scenario. I found this online which can help you, just like me, on looking unto a database that has no documentation.

DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_schema SYSNAME,
@column_name SYSNAME,
@sql_string VARCHAR(2000)

SET @search_string = ‘Test’

DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL — Only strings have this and they always have it

OPEN columns_cur

FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = ‘IF EXISTS (SELECT * FROM ‘ + QUOTENAME(@table_schema) + ‘.’ + QUOTENAME(@table_name) + ‘ WHERE ‘ + QUOTENAME(@column_name) + ‘ LIKE ”%’ + @search_string + ‘%”) PRINT ”’ + QUOTENAME(@table_schema) + ‘.’ + QUOTENAME(@table_name) + ‘, ‘ + QUOTENAME(@column_name) + ””

EXECUTE(@sql_string)

FETCH NEXT FROM columns_cur INTO @column_name
END

CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END

CLOSE tables_cur

DEALLOCATE tables_cur

God Bless!

Thanks,
Thomie

MSSQL : Rename DB that is having Can’t Exclusively Locked

Do you want to rename DB that is having can’t exclusively locked error message.

 

Try this.

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbnameMODIFY NAME = [dbname.DevData]
ALTER DATABASE [dbname.DevData]
SET MULTI_USER WITH ROLLBACK IMMEDIATE

 

God Bless!

MSSQL – Execution Plan Caching and Recompiling

Hi,

Have you every experience that you have a stored procedure(SP) that have plenty of parameters that when 1 set of parameter is executed for the very first time the said SPwas created, its fast. Then after you have a new set of parameter passed to the same SP you’ll end up a long execution process?

Then that is because of a so called Execution Plan Caching that MSSQL is doing on your query. In an overview MSSQL check if there is an existing execution plan for the SP. If there is none, it will create an optimized execution plan, compile, and then cache it on memory. So when the same SP is executed with the same number of parameter it will be reused.

So in cases that you have the same number of parameter every time you use the said stored procedure and you know that you have a logic that will change the query result then this optimization may not be on our side.

When does this caching is cleared? It is only cleared when either there are no memory allocation for MSSQL or you force MSSQL to recompile the SP.

How to Recompile your specific SP? Just add OPTION (RECOMPILE) at the end of your query, before the END statement, and you are ready to go. In some cases you may want to force all cache to be removed, you need to use another statement to do that and that is DBCC FREEPROCCACHE.

That’s it. Hope it helped you as it helped me on my project.

God Bless!
Thanks,
Thomie

MSSQL – Drop all SP and Table on a Database

Execute this with Precaution

USE YourDataBaseName
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
EXEC sp_MSforeachtable @command1 = “DROP TABLE [?]”

 

 

MSSQL – Count Number of Records on Each Table

Here is the script to count all the number of records on each table on a MSSQL Server:

 

SELECT sc.name +’.’+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Hope it helps.

God Bless!
Thanks,
Thomie

Opencast, Your Free and Open Video Casting!

Hi There,

We are glad to invite you to our first Free service product, OpenCast. OpenCast is an online broadcasting websites that aim to help people broadcast freely and openly. The website is created using ASP.Net MVC, Entity Framework, SignalR, & OpenTok.

 

Feel Free to register now at http://opencast.info

God Bless!

Thanks,
Thomie

MS SQL Search by Column of Database

MS SQL Search by Column of Database

USE DBNAME
GO

SELECT

t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE ‘%COLUMN_NAME_HERE%’

ORDER BY schema_name, table_name;