HOW TO FIND LOCKED TABLE IN SQL

QUERY TO FIND THE LOCKED TABLE:-

select  
    object_name(P.object_id) as TableName, 
    resource_type, resource_description
from
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

VERY INTERESTING TOOL TO SEARCH SQL DATABASE

Here is the download link:-

http://www.2shared.com/file/EJwRuYvK/SQLSearch_11957.html

HOW TO WRITE A TRIGGER?

Hi,

CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
AS
INSERT INTO T2 (PK, OldValue, NewValue)
SELECT i.PK, i.Value, d.Value
FROM INSERTED i INNER JOIN DELETED d ON i.PK = d.PK

HOW TO FIND LONGEST RUNNING QUERY IN SQL

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO

How do I find the columns that depend on a specified CLR user-defined type or alias type?

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,c.name AS column_name 
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,TYPE_NAME(c.user_type_id) AS user_type_name
    ,c.max_length
    ,c.precision
    ,c.scale
    ,c.is_nullable
    ,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID('<schema_name.data_type_name>'); 
GO

WHAT IS THE USE OF MSDB DATABASE

Hi ,


I was asked with a question about the use of taking backup of MSDB system database in SQL??

I am explaining the same for viewers:-

There are Four System databases in SQL server 2000 and later versions

Master DB  :  The use of this DB is most important coz if this DB corrupts then whole user databases are get                        into affect , atleast once in a month taking BU of Master DB is good practice


MSDB :    what ever the Jobs/Plans/Alerts running on the User DBs are related with the database.....Usually if we take the backup of database these jobs/plans not get back up..So it is very very important that if we need to restore the jobs then u have to take the backup MSDB

Querying the resultset of a stored proc into a temp table

declare @lines table (id int identity(1, 1) primary key, line varchar(8000));

insert into @lines(line)
    exec sp_executesql N'sp_helptext ''information_schema.tables''';

select *
from @lines
order by id; 

www.varuntechnologies.in

How to SELECT * INTO [temp table] FROM [stored procedure]

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.d


atabases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

How to select rows with no matching entry in another table?

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

www.varuntechnologies.in