Tag: stored procedures

Pro Members SQL Server Standard Members

Useful Undocumented SQL Server 2016 Stored Procedures (Part 4)

Useful Undocumented SQL Server 2016 Stored Procedures (Part 4) SQL Server 2016 supports the following useful undocumented stored procedures: sp_check_log_shipping_monitor_alert sp_create_log_shipping_monitor_account sp_log_shipping_get_date_from_file sp_MSget_jobstate sp_get_job_alerts sp_multi_server_job_summary sp_help_operator_jobs sp_check_log_shipping_monitor_alert The sp_check_log_shipping_monitor_alert log shipping stored procedure is used to check all monitor alerts in the primary database and in the secondary database in log shipping configuration. Syntax sp_check_log_shipping_monitor_alert Return Code Values 0 (success)...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Useful Undocumented SQL Server 2016 Stored Procedures (Part 3)

Useful Undocumented SQL Server 2016 Stored Procedures (Part 3) SQL Server 2016 supports the following useful undocumented stored procedures: sp_catalogs_rowset2 sp_catalogs_rowset_rmt sp_linkedservers_rowset sp_fulltext_recycle_crawl_log sp_MShelpfulltextindex sp_MShelpfulltextscript sp_catalogs_rowset2 The sp_catalogs_rowset2 distributed queries stored procedure returns the list of the databases the current user has access to. Syntax sp_catalogs_rowset2 Return Code Values None. Result Sets Column name Data type Description CATALOG_NAME nvarchar(128) Is...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
SQL Server

Tips for using stored procedures in SQL Server 2016

Tips for using stored procedures in SQL Server 2016 Try to avoid using temporary tables and DDL (Data Definition Language) statements inside your stored procedure. Using temporary tables or DDL statements inside stored procedure reduces the chance to reuse the execution plan. Consider using user-defined table type as a parameter for the stored procedures. SQL Server 2016 supports a user-defined […]

SQL Server

Three Undocumented SQL Server 2014 XML Stored Procedures

Three Undocumented SQL Server 2014 XML Stored Procedures SQL Server 2014 supports the following three undocumented XML system stored procedures: sp_syscollector_validate_xml sp_xml_schema_rowset sp_xml_schema_rowset2 sp_syscollector_validate_xml The sp_syscollector_validate_xml system stored procedure is used to validate the XML collector. Syntax sp_syscollector_validate_xml [ @collector_type_uid = ] ‘collector_type_uid’, [ @name = ] ‘name’, [ @parameters = ] ‘parameters’ Arguments [ @collector_type_uid = ] ‘collector_type_uid’ Is […]

SQL Server

Five Undocumented SQL Server 2014 Job Stored Procedures

Five Undocumented SQL Server 2014 Job Stored Procedures SQL Server 2014 supports the following useful undocumented SQL Server job stored procedures: sp_MSget_jobstate sp_delete_all_msx_jobs sp_get_job_alerts sp_multi_server_job_summary sp_help_operator_jobs One of these stored procedures exist in the master database (sp_MSget_jobstate), other exist in the msdb database. sp_MSget_jobstate The sp_MSget_jobstate stored procedure returns the job state for the specified job. Syntax sp_MSget_jobstate [ @job_id […]

SQL Server

Six Undocumented SQL Server 2014 Database Engine Stored Procedures

Six Undocumented SQL Server 2014 Database Engine Stored Procedures SQL Server 2014 supports the following useful undocumented database engine stored procedures: sp_bcp_dbcmptlevel sp_MSforeachdb sp_MSforeachtable sp_MSindexspace sp_MStablespace sp_objectfilegroup sp_bcp_dbcmptlevel The sp_bcp_dbcmptlevel database engine stored procedure is used to get the database compatibility level for the specified database. Syntax sp_bcp_dbcmptlevel [ @dbname = ] ‘dbname’ Arguments [ @dbname = ] ‘dbname’ The […]

SQL Server

Five Undocumented SQL Server 2014 OLEDB Stored Procedures

Five Undocumented SQL Server 2014 OLEDB Stored Procedures SQL Server 2014 supports the following useful undocumented oledb stored procedures: sp_oledb_database sp_oledb_defdb sp_oledb_deflang sp_oledb_language sp_oledb_ro_usrname sp_oledb_database The sp_oledb_database stored procedure returns the oledb database name. Syntax sp_oledb_database Return Code Values None. Result Sets Column name Type Description name sysname The name of the oledb database Remarks This stored procedure exists in […]

SQL Server

Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures

Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures SQL Server 2014 supports the following useful undocumented database maintenance plan stored procedures: sp_clear_dbmaintplan_by_db sp_maintplan_close_logentry sp_maintplan_delete_log sp_maintplan_delete_plan sp_maintplan_delete_subplan sp_maintplan_open_logentry sp_maintplan_start sp_maintplan_subplans_by_job sp_maintplan_update_subplan_tsx Note. These stored procedures exist in the msdb database, not in master. sp_clear_dbmaintplan_by_db The sp_clear_dbmaintplan_by_db database maintenance plan stored procedure is used to delete the maintenance plans, the […]

SQL Server

Three Undocumented SQL Server 2014 Full-Text Search Stored Procedures

Three Undocumented SQL Server 2014 Full-Text Search Stored Procedures SQL Server 2014 supports the following three undocumented full-text search system stored procedures: sp_fulltext_recycle_crawl_log sp_MShelpfulltextindex sp_MShelpfulltextscript sp_fulltext_recycle_crawl_log The sp_fulltext_recycle_crawl_log system stored procedure is used to recycle crawl log for the given full-text catalog name. Syntax sp_fulltext_recycle_crawl_log [ @ftcat = ] ‘ftcat’ Arguments [ @ftcat = ] ‘ftcat’ Is the full-text catalog […]