SQL Server Jobs Execution information

Hi here is the script to find the  SQL Server Job Execution information. This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

   1:  

   2:  

   3: SELECT      [JobName]   = JOB.name,

   4:             [Step]      = HIST.step_id,

   5:             [StepName]  = HIST.step_name,

   6:             [Message]   = HIST.message,

   7:             [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

   8:                                WHEN HIST.run_status = 1 THEN 'Succeeded'

   9:                                WHEN HIST.run_status = 2 THEN 'Retry'

  10:                                WHEN HIST.run_status = 3 THEN 'Canceled'

  11:                                END,

  12:             [RunDate]   = HIST.run_date,

  13:             [RunTime]   = HIST.run_time,

  14:             [Duration]  = HIST.run_duration

  15: FROM   msdb..sysjobs JOB

  16: INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id

  17: WHERE HIST.run_date=convert(varchar,getdate(),112)

  18: ORDER BY    HIST.run_date, HIST.run_time 

  19:  

  20: /* WHERE    JOB.name = '<job name>' 

  21:    WHERE HIST.run_date='<yyyymmdd>' */

  22:  

  23:  

List of Database Objects in SQL Server

Hi,  I would like post on SQL Scripts which I use mostly to get list of database objects like Tables,Views,Triggers,Functions etc.,

List of Views in a database

   1: /*************************List of views in DB************************/

   2: -- using INFORMATION_SCHEMA:

   3: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION 

   4: FROM INFORMATION_SCHEMA.Views

   5: ORDER BY TABLE_NAME;

   6:  

   7: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   8: FROM INFORMATION_SCHEMA.TABLES 

   9: WHERE TABLE_TYPE='VIEW'

  10: ORDER BY TABLE_NAME;

  11:  

  12: -- using the sp_tables system stored procedure:

  13: EXEC sp_tables @table_type = "'VIEW'";

  14:  

  15: -- using the system tables:

  16: SELECT name

  17: FROM sysobjects

  18: WHERE xtype = 'V';

  19:  

  20: -- using the catalog views:

  21: SELECT name FROM sys.views;

 

List of Tables in a database

   1: /*******************List of user defined tables in DB***************/

   2: -- using sp_tables stored procedure:

   3: EXEC sp_tables @table_type = "'TABLE'";

   4:  

   5: -- using INFORMATION_SCHEMA:

   6: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   7: FROM INFORMATION_SCHEMA.TABLES

   8: WHERE TABLE_TYPE = 'BASE TABLE'

   9: ORDER BY TABLE_SCHEMA,TABLE_NAME;

  10:  

  11: -- using the system tables:

  12: SELECT name

  13: FROM sysobjects

  14: WHERE xtype = 'U';

  15:  

  16: -- using the catalog views:

  17: SELECT NAME FROM sys.tables;

 

List of Users in a database

   1: /*******************List of Users in DB*********************/

   2: SELECT name FROM sysusers;

 

List of Indexes on a Table

   1: /*******************List indexes on Table***************************/ 

   2: EXEC sp_helpindex '<table with schema>';

 

List of Constraints on a table and its details

   1: /*******************List Constraints on Table*************************/ 

   2: EXEC sp_helpconstraint '<table with schema>';

   3:  

   4: /**********************Constraint Information*************************/

   5: SELECT k.table_name,

   6: K.TABLE_SCHEMA,

   7:  k.column_name field_name,

   8:  c.constraint_type,

   9:  CASE c.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

  10:  CASE c.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

  11:  rc.match_option 'match_type',

  12:  rc.update_rule 'on_update',

  13:  rc.delete_rule 'on_delete',

  14:  ccu.table_name 'references_table',

  15:  ccu.column_name 'references_field',

  16:  k.ordinal_position 'field_position'

  17:  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k

  18:  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

  19:  ON k.table_name = c.table_name

  20:  AND k.table_schema = c.table_schema

  21:  AND k.table_catalog = c.table_catalog

  22:  AND k.constraint_catalog = c.constraint_catalog

  23:  AND k.constraint_name = c.constraint_name

  24: LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

  25:  ON rc.constraint_schema = c.constraint_schema

  26:  AND rc.constraint_catalog = c.constraint_catalog

  27:  AND rc.constraint_name = c.constraint_name

  28: LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

  29:  ON rc.unique_constraint_schema = ccu.constraint_schema

  30:  AND rc.unique_constraint_catalog = ccu.constraint_catalog

  31:  AND rc.unique_constraint_name = ccu.constraint_name

  32:  WHERE k.constraint_catalog = DB_NAME()

  33:  AND k.table_name = '<table name>'

  34:  AND k.TABLE_SCHEMA='<schema name>'

  35:  AND k.constraint_name = '<Constraint Name>'

  36:  ORDER BY k.constraint_name,

  37:  k.ordinal_position;

 

List of Columns in a Table and its details

   1: /********************************List of Table fields******************/

   2: --Using system tables 

   3: SELECT c.name,o.name

   4:  FROM sys.columns c

   5:  JOIN sys.objects o ON c.object_id = o.object_id

   6:  WHERE o.name = '<Table Name>'

   7:  AND o.schema_id=<Schema Name>

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT column_name,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME

  11:  FROM INFORMATION_SCHEMA.COLUMNS

  12:  WHERE table_name = '<Table Name>'

  13:  AND TABLE_SCHEMA='<Schema Name>';

  14:  

  15: --Using System procedures for details information of table

  16: EXEC sys.sp_help '<Table with Schema>';

 

List of Triggers in a database and its details

   1: /*********************List of Triggers for a given table****************/

   2: --Using sysobjects table

   3: SELECT o.name

   4:  FROM sysobjects o

   5:  WHERE xtype = 'TR'

   6:  --AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

   7:  AND object_name(parent_obj) = '<Table Name>';

   8:  

   9: --Using sys.triggers table

  10: SELECT name

  11:  FROM sys.triggers

  12:  WHERE is_ms_shipped = 0

  13:  AND object_name(parent_id) = '<Table Name>';

  14:  

  15:  --using System procedure

  16:  EXEC sys.sp_helptrigger '<Table Name with Schema>';

  17:  

  18: --detail information of a Trigger

  19: SELECT sys1.name trigger_name,sys2.name table_name,c.text trigger_body

  20: ,c.encrypted is_encripted,

  21:        CASE

  22:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1 

  23:         THEN 0 ELSE 1 END trigger_enabled,

  24:        CASE

  25:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT'

  26:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE'

  27:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1 

  28:         THEN 'DELETE' END trigger_event,

  29:        CASE 

  30:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1 

  31:         THEN 'INSTEAD OF' ELSE 'AFTER' END trigger_type

  32: FROM sysobjects sys1

  33: JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id

  34: JOIN syscomments c ON sys1.id = c.id

  35: WHERE sys1.xtype = 'TR';

  36:  

 

List of Functions in a Database

   1: /*****************List of Function in DB***************************/

   2: --Using System tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('TF', 'FN', 'IF','AF');

   6:  

   7: -- with INFORMATION_SCHEMA:

   8: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

   9:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  10: FROM INFORMATION_SCHEMA.ROUTINES

  11: WHERE routine_type = N'FUNCTION';

  12:  

  13: --To modify function

  14: EXEC sys.sp_helptext '<Function Name>'

 

List of Procedures in a database

   1: /*****************List of Procedures in DB***************************/

   2: --Using System Tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('P', 'RF', 'X', 'PC');

   6:  

   7: SELECT name FROM sys.procedures;

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

  11:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  12: FROM INFORMATION_SCHEMA.ROUTINES

  13: WHERE routine_type = N'PROCEDURE';

  14:  

  15: --To Modify Procedures

  16: EXEC sys.sp_helptext '<Procedure Name>'

Number of character occurrences in String

To find number of character occurrences in a String.

To test, execute the function and ..

Convert alphanumeric to numeric

Below script is to convert alpha numeric mobile numbers to numeric

here I have given mobile number as ‘1-800-MSFT’

Result is:

Generate insert statements of records in a table

We may need to transfer records from Development to Production, we have many ways to do that, let me show how to generate ‘Insert statements’ using StoredProcedure for the required SQL Server table from which we need to transfer data.

download ‘GenerateInsertStatements‘ script to generate ‘Insert Statement’ of a SQL Server Table

Let me show how it works…

Script need two parameters ‘table name’ and ‘schema name’, if we submit those two parameters it will generate ‘Insert statements’ for the given table.

Another case here is to consider IDENTITY column while generating insert data script. Let’s revise the above example by considering Id as identity column.

I have attached the procedure, just try using the procedure in your free time…

Know about costliest query running right now

• By Memory usage

• By Query cost.

Scripts to find details about ‘logins’ & ‘Roles’

Script to find server level logins and role assigned

 

Script to find database users and roles assigned

 

 

 

Script to find Object level permission for user databases

Execute batch of .sql scripts in a folder.

• This is the script to execute batch of .SQL  scripts.
• Scripts are located in the C:\SQL Scripts directory and they have a file extension of .sql.
• Enable xp_cmdshell property.

Script to do regular backup of a Database

This script is used to do regular backups of a given database when running as a scheduled sql job. It appends the date to each backup to prevent conflicts.

Clear the transaction logs of a database

• During development these can get pretty excessive.

 

 

 

 

• This will stop the transaction logs from growing too large. It is also a good idea to do regular backups of these logs (which shrinks them anyway)