How to Find Database Changes in SQL Server

-- Find Stored Procedure Changes

SELECT create_date, modify_date, name FROM sys.procedures
ORDER BY modify_date DESC

-- Find user Defined function Changes
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'fn' ORDER BY modify_date DESC


SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo'
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo'
order by  CREATED desc






-- Find modified and new added columns list from particular table In sql server 2008

 SELECT
    OBJECT_NAME(sc.[object_id]) as [table]
    ,sc.[name] as [column]
    ,so.modify_date
    ,so.create_date
  FROM [sys].[columns] sc
  JOIN [sys].[objects] so
  ON sc.[object_id] = so.[object_id]
  ORDER BY so.modify_date DESC, so.create_date ASC

-- Find Count Of User Defined Table and Stored Procedure in particular Database.

SELECT
    CASE TYPE
        WHEN 'U'
            THEN 'User Defined Tables'
        WHEN 'S'
            THEN 'System Tables'
        WHEN 'IT'
            THEN 'Internal Tables'
        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
    END,
    COUNT(*)   
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE

---
How to identify all stored procedures referring a particular table


select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
                    and sp.type in ('P', 'FN')
  where o.name = 'TestTable'
  order by sp.Name



Find all stored procedures that reference a specific column in some table


SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%'
GO

Find all table names with column name?

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnName' + '%'
GO


Need to list all triggers in SQL Server database with table name and table's schema

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'
EDIT: Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
EDIT 2: For SQL 2000

SELECT
     o.name AS trigger_name
    ,'x' AS trigger_owner
    /*USER_NAME(o.uid)*/
    ,s.name AS table_schema
    ,OBJECT_NAME(o.parent_obj) AS table_name
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sysobjects AS o2
    ON o.parent_obj = o2.id

INNER JOIN sysusers AS s
    ON o2.uid = s.uid

WHERE o.type = 'TR'

Need to list all triggers in SQL Server database with table name and table's schema

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'
EDIT: Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/ 
INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
EDIT 2: For SQL 2000

SELECT
     o.name AS trigger_name
    ,'x' AS trigger_owner
    /*USER_NAME(o.uid)*/
    ,s.name AS table_schema
    ,OBJECT_NAME(o.parent_obj) AS table_name
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o
/*
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
*/ 
INNER JOIN sysobjects AS o2
    ON o.parent_obj = o2.id

INNER JOIN sysusers AS s
    ON o2.uid = s.uid

WHERE o.type = 'TR'

Extracting substring between two characters OR Delimeters using TSQL,SQLServer 2008

sample values in that Username column:-

1)  <Name=test>

2)  <Name=testKing>

3)  <Name=King>

Sql Query

select SUBSTRING(loginname,CHARINDEX('=',Username )+1,CHARINDEX('>',Username )-CHARINDEX('=',Username )-1)

Result 

  1. test

  2. testKing

  3. King

Log record changes in SQL server in an audit table

1) Create AUDIT Table.

IF NOT EXISTS
      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
               AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
       CREATE TABLE Audit
               (Type CHAR(1),
               TableName VARCHAR(128),
               PK VARCHAR(1000),
               FieldName VARCHAR(128),
               OldValue VARCHAR(1000),
               NewValue VARCHAR(1000),
               UpdateDate datetime,
               UserName VARCHAR(128))
GO


2) CREATE an UPDATE Trigger on the GUESTS Table as follows.

CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
AS

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000)


--You will need to change @TableName to match the table to be audited. 
-- Here we made GUESTS for your example.
SELECT @TableName = 'GUESTS'

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

-- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = 'U'
       ELSE
               SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') 
       + '''<' + COLUMN_NAME 
       + '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
       RAISERROR('no PK on table %s', 16, -1, @TableName)
       RETURN
END

SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
                                       OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field
               SELECT @sql = '
INSERT INTO AUDIT (    Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName)
select ''' + @Type + ''',''' 
       + @TableName + ''',' + @PKSelect
       + ',''' + @fieldname + ''''
       + ',convert(varchar(1000),d.' + @fieldname + ')'
       + ',convert(varchar(1000),i.' + @fieldname + ')'
       + ',''' + @UpdateDate + ''''
       + ',''' + @UserName + ''''
       + ' from #ins i full outer join #del d'
       + @PKCols
       + ' where i.' + @fieldname + ' <> d.' + @fieldname 
       + ' or (i.' + @fieldname + ' is null and  d.'
                                + @fieldname
                                + ' is not null)' 
       + ' or (i.' + @fieldname + ' is not null and  d.' 
                                + @fieldname
                                + ' is null)' 
               EXEC (@sql)
       END
END


GO

SQL Server : trigger how to read value for Insert, Update, Delete

Logical Tables in Sql Server

Data Manipulation Language (DML) Triggers have access to the two special logical Tables named INSERTED and DELETED. These are the temporary tables managed by Sql Server. Structure of these tables will be same as that of the table on which the DML action is fired and holds the old and new values of the rows which are modified by the DML statement.

DELETED logical table will hold the rows which are deleted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE or UPDATE operation. An UPDATE DML operation is internally executed as first the deletion of the existing record and then insertion of the new record.

INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.

SELECT
  CASE WHEN inserted.primaryKey IS NULL THEN 'This is a delete'
       WHEN  deleted.primaryKey IS NULL THEN 'This is an insert'
                                        ELSE 'This is an update'
  END  as Action,
  *
FROM
  inserted
FULL OUTER JOIN
  deleted
    ON inserted.primaryKey = deleted.primaryKey

how to select a particular tab from other page using JQuery..?

<ul class="nav nav-tabs" id="MyTabs">
        <li><a href="#tab1">Flight</a></li>
        <li id="liHotels"><a href="#tab2">Hotels</a></li>
        <li><a href="#tab3">International Flight</a></li>
        <li id="liRail"><a href="#tab4">Rail</a></li>
        <li><a href="#tab5">Bus</a></li>
</ul>

<div id="tab1" class="tab_content">1</div>
<div id="tab2" class="tab_content">2</div>
<div id="tab3" class="tab_content">3</div>
<div id="tab4" class="tab_content">4</div>
<div id="tab5" class="tab_content">5</div>


<script type="text/javascript">
 $(document).ready(function () {
 // to get Querystring  Parameter Value On Page URL
  // http://www.example.com?searchTab=hotel
if (getParameterByName('searchTab') == 'hotel' ) {             
           
                // To Activate Hotels Tab
                $('#MyTabs li').removeClass("active");
                $('#liHotels').addClass("active");
                $('#MyTabs a[href="#tab2"]').attr("aria-expanded", "true");
                $('#tab2').addClass('tab_content active in');

                // To Remove all Tabs except Hotel Tab
                $('#tab1').removeClass();
                $('#tab3').removeClass();
                $('#tab4').removeClass();
                $('#tab5').removeClass();
                $('#tab1').addClass('tab_content');
                $('#tab3').addClass('tab_content');
                $('#tab4').addClass('tab_content');
                $('#tab5').addClass('tab_content');
            }

});

   function getParameterByName(name, url) {
            if (!url) {
                url = window.location.href;
            }
            name = name.replace(/[\[\]]/g, "\\$&");
            var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
                results = regex.exec(url);
            if (!results) return null;
            if (!results[2]) return '';
            return decodeURIComponent(results[2].replace(/\+/g, " "));
        }
<script type="text/javascript">

How the traditional ASP.NET application works

When the user sends a request to IIS, it validates the requested file extension & creates an application domain where the app can run. Later HttpApplication object is created once after all the HTTP object such as HttpContext, HttpRequest and HttpResponse are created. Then the request is processed by the two prominent pillars called HttpModule and HttpHandlers.