Learn database in a simple and easy way.
SQL Triggers – An Introduction Contents Introduction Magic Tables Difference between Stored Procedure and Trigger DML Triggers After Triggers Syntax of the After trigger Example of After Trigger for Insert Example of After Trigger for Delete Example of After Trigger for Update Instead of Triggers DDL Triggers DDL Trigger for Create Table DDL Trigger for […]
Continue ReadingSQL Script to find triggers in SQL Database Sometime we need to find all the triggers defined on the database. So in this case, we can use the below SQL Query:-SELECT tbl.name as [Table Name] , trig.name as [Trigger Name] , trig.is_disabled FROM [sys].[triggers] as trig INNER JOIN sys.tables as tbl ON trig.parent_id = tbl.object_idIn […]
Continue Reading
Through this article, we are going to discuss three important interview questions of SQL which are given below:- 1) Can we create clustered index on a column containing duplicate values? 2) Can we create a Primary Key on a table on which a clustered index is already defined? 3) If a clustered index is already defined on a […]
Continue ReadingBest Practices in database programming For any programming language, just writing the code is not well enough. It should be written using the best practices. This article Best Practices in database programming I will try to explain the disadvantages of writing code without using Best Practices and later on how best practices can be implemented […]
Continue ReadingScript to find the missing indexes Performance tuning in SQL is important exercise and index creation is an important part of it. Sometimes base on the frequent SQL queries, we need to create some indexes or missing indexes which are enhance the query performance. Below script will help in finding the missing indexes. SELECT db_name(d.database_id) dbname , object_name(d.object_id) […]
Continue ReadingOne of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday. SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS ‘Failure Date’, SUBSTRING(T2.name,1,40) AS ‘Job Name’, T1.step_id AS ‘Step_id’, T1.step_name AS ‘Step […]
Continue ReadingSometimes we need to find out how many stored procedures contains a given text. Below SQL query can be used to find out the list of all the stored procedures which contains a particular given text as input. SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE ‘%Given text%’
Continue ReadingBelow is the SQL query which can be used to find out the tables which are created or modified on or from a given date. SELECT [name] as Tablename ,create_date ,modify_date FROM sys.tables where cast(create_date as date)>=’Given date’
Continue ReadingSometimes we need to find out the size of the database on a server. Below SQL Scripts can be used to find out the size of all the databases created on the server. ;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize) AS ( SELECT @@SERVERNAME SqlServerInstanceName, db.name AS […]
Continue ReadingBelow query can be used to see the growth size of database files. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); — Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; — rip […]
Continue Reading