Helpful Queries

Hi,

There are some queries which will be help full many times 

Below query will give the list of dependent objects on a table 

SELECT DISTINCT

SYSOBJECTS.NAME 'TABLE NAME',

PROCEDURES.NAME 'STORED PROCEDURE'

FROM SYSOBJECTS

JOIN (SYSOBJECTS PROCEDURES

JOIN SYSDEPENDS

ON PROCEDURES.ID = SYSDEPENDS.ID)

ON SYSDEPENDS.DEPID = SYSOBJECTS.ID

WHERE SYSOBJECTS.NAME = 'TABLE NAME'

Below query will help to find the which record is locked

        

SELECT [Table Name] FROM [Table Name] WITH(NOLOCK)

WHERE ID NOT IN

(

    SELECT ID FROM [Table Name] WITH(READPAST)

)

Below query will help to find who is looged in into database

          

SELECT host_name,session_id,login_time

FROM sys.dm_exec_sessions a

inner join sysdatabases b on a.database_id = b.dbid

where name = 'database name'

Below query will help to find in how many tables particular column is used.

     

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

 WHERE c.name LIKE '%ColumnName%' and SCHEMA_NAME(schema_id) = 'Schema Name' ORDER BY schema_name, table_name;

Below query will give us all the database name, creation date and file path in SQL-Server.

     

SELECT

name AS DBName,

crdate AS [Creation Date],

[filename] AS [File Path]

FROM

SYSDATABASES