Friday 24 May 2013

Some Important SQL Scripts

 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.
   
  
                   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


image
To test, execute the function and ..
image

Convert alphanumeric to numeric


Below script is to convert alpha numeric mobile numbers to numeric
image
here I have given mobile number as ‘1-800-MSFT’
Result is:
image

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
image
  • By Query cost.
image

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


Script to find server level logins and role assigned
image

Script to find database users and roles assigned
image



Script to find Object level permission for user databases
image

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.
image

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.

image

Clear the transaction logs of a database

  • During development these can get pretty excessive.
image




  • 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)
image

No comments:

Post a Comment