Category Archives: MSSQL

MSSQL Grant Execute Stored Procedure only

Leave a reply

There are cases that we only want to grant read-write only access then also enable users to execute stored procedure with in the SQL Server database.

In order to do this we will need to:

  1. Create a new Role named, db_exec_storedprocedure

    CREATE ROLE db_exec_storedprocedure
  2. Grant that role with execute stored procedure

    GRANT EXECUTE TO db_exec_storedprocedure
  3. Then add the user as a member of that role.

That’s it you can now grant specific permission to execute stored procedure.

God Bless!
Thanks,
Thomie

Tip: MSSQL Drop all triggers

Leave a reply

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

Leave a reply

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

Leave a reply

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 – Execution Plan Caching and Recompiling

Leave a reply

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

Leave a reply

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

Leave a reply

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!

Leave a reply

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

[MSSQL] Truncate all Table

Leave a reply

Truncating all tables is not easy by default on MSSQL so I have search the net and found one. Just open up a new query and use the following codes:

Use databaseName
GO

EXEC sp_MSForEachTable ‘TRUNCATE TABLE ?’
GO

It will truncate all the tables under the databaseName DB.

God Bless!

Thanks,
Thomie