Finding out Table in each Database of a Server

Below query is used to find out a particular table in each Database of a server.

It will loop through each database and checks for the particular table.

EXEC sp_Msforeachdb "use [?];select '[?]' as DatabaseName, * from sys.tables where name='TableName' "

Finding Out the Oldest Active Transaction

Following query is used to check the oldest active transactions on a DB. Open Transactions blocks tables and queries will not return any result.

Let’s look at example.

CREATE TABLE [dbo].[emp](

      [empid] [int] IDENTITY(1,1) NOT NULL,

      [empname] [varchar](10) NULL,

      [role] [varchar](10) NULL

) ON [PRIMARY]

Lets begin a transaction…

begin tran

insert into emp

values ('Pranay','MGR')

without firing commit or roll back fire the below query

dbcc opentran

This command will give us the oldest active transaction in DB.

System level Stored Procedure

All system level stored procedures are stored in MSDB or MASTER Databases, and most begins with characters SP_  . Here is synopsis of the more common system stored procedures.

Sp_tables	List down all the tables and views
Sp_stored_procedures	Lists down all the stored procedures
Sp_server_Info	Provides the server level information such as Version,character set…
Sp_databases	List down all availbale databases on server
Sp_start_job	To start sql agent job
Sp_stop_job	To stop sql agent job
Sp_monitor	Gives a quick snapshot of how server is doing. How much RAM is in use,how busy processor is…
Sp_who	Used to check blocks,who is using a database…
Sp_rename	To change name of any object
Sp_renamedb	Change the name of DB
Sp_help	To find out information about any object in database.
Sp_helptext	This is used to display the actual text that was used to create an object in database.
Sp_addlogin	To add a standard login to the server
Sp_grantlogin	To grant access on sql server to a windows account
Sp_password	Used to change password for standard login

Query to findout SQL Agent Jobs which uses SSIS packages

Following Query is used to get the list of SQL Agent Jobs which call SSIS packages from
them

USE [msdb]

GO

SELECT

      Job.name AS JobName,

      JStep.step_id,

      JStep.step_name AS StepName,

      JStep.command,

      Job.enabled

FROM   dbo.sysjobs Job

JOIN   dbo.sysjobsteps JStep

      ON  JStep.job_id = Job.job_id

WHERE  JStep.subsystem='SSIS'

      ORDER BY Job.name,step_id