Category Archives: Database

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

Don’t Ever use Inline Database in MSSQL SP and Query

Leave a reply

Hi there!

I have been modifying a source code from my work and it has been a little difficult to test the system in multiple instances of database. This is due the previous developers of the said source code used Inline Query Codes and Connection Strings. In addition to that this Inline Queries and even their Stored Procedure use Inline Database, an example is [Database1].[dbo].[tbl_tableName] which should not be in practice if it is using the same database on the whole coding system.

This kind of practice limits the system to use different database name. For an instance you have a web server that serves both Development and Production Environment. You have a project that needs to have Dev and Prod on the said server. If this practice is used you are limited to one Environment at a time nevertheless you modify each script to change that inline database name. So its better to use [dbo].[tbl_tableName]  than [Database1].[dbo].[tbl_tableName] 

Thanks,

Thomie

Delete all MS SQL Stored Procedure

4 Replies

Here is a code that you can user to delete all ms sql stored procedure

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur

MS SQL Generate Script with Data

2 Replies

Here is a step on how you can make MS SQL Generate Script with Data.

  1. Open your Sql Server Management Studio.
  2. Select your database and right click on it follow just like what is in the image below. Refer to image 1
  3. Click Next until you when thru to the image below and click Advance. Refer to image 2
  4. Scroll down and until this row and update the value just like what is below. Refer to image 3
  5. Click Ok and next until you have generated the said script. Refer to image 4
  6. Now you have a create script that includes the schema at it’s data.