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.
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.
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
List of Tables in a database
List of Users in a database
List of Indexes on a Table
List of Constraints on a table and its details
List of Columns in a Table and its details
List of Triggers in a database and its details
List of Functions in a Database
List of Procedures in a database
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>'
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…
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…
No comments:
Post a Comment