This is a collection of articles about SQL Server database administration that were formerly published on this blog. This is for quick reference.
sys.objects “type” abbreviations and their meanings – SQL Server
A system table or catalog table that stores “System Objects” is called sys.objects. Every user-defined, schema-scoped object that is created in a database is represented by a row, including any natively built scalar user-defined functions. The columns such as “type” has abbreviated information about the type of the object. Their meaning has been given below.…
Sys.Procedures in SQL Server
The subset of sys.objects with the object types = P, X, RF, and PC can be found in the object catalog view sys.procedures. P = SQL Stored Procedure X = Extended stored procedure RF = Replication-filter-procedure PC = Assembly (CLR) stored-procedure In other words, the columns of this view (sys.procedures) inherit from sys.objects. By executing the…
SQL Server System Databases
The databases that are loaded with SQL Server are known as system databases. These are used to store system-level data, metadata data, data about scheduled jobs and alerts, etc. There are five system databases; four of them will be shown in the object explorer. master Database Records all the system-level information for an instance of…
List of indexes with Index Type – SQL Server
The system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes function. The following query is used to acquire a list of a specific database’s indexes…
SQL Server Error Log – Search
The SQL Server error log contains user-defined events and certain system events you can use for troubleshooting. The errors and the informational data will be stored in the error logs. The data will be huge and sometimes it will be difficult to find the information or error from it. Below is the query to retrieve…
List of indexes from a specific table – SQL Server
An earlier post described that the system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes. However, you can do so by executing the following statement…
SET ANSI_NULLS ON – SQL Server
The setting for ANSI NULLS should be ON while running distributed queries. When creating or altering indexes on calculated columns or indexed views, ANSI NULLS must also be ON. Any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on calculated columns or indexed views will fail if SET ANSI NULLS is set to…
SQL Server – List-out Procedures, Functions, Triggers
In order to know which procedures, functions, and triggers exist in the database, the following queries can help you to find this out.: The below set of statements can be used to retrieve stored procedures and the number of parameters in it: Use [Test1] Go SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME) FROM INFORMATION_SCHEMA.PARAMETERS GROUP BY SPECIFIC_NAME The below…
Find a specific column from the database objects
There are many ways to find out a specific column from the database objects like Tables and stored procedures. 1) Red Gate’s SQL Search:This tool will be integrated into SQL Server Management Studio once installed. This will help in searching across multiple object types and multiple databases. 2) Using a query:The following query will find the…
Find SQL scheduled jobs to run during a specific time range
There are times when we need to know which jobs are going to run in the upcoming half an hour or so. It is quite possible and easy when you invoke the job activity monitor. However, if you want to know through a query or if you want to make a report, here is the code.SELECT…
SQL Server – Job Completion Status
One of the often-used scripts that DBAs keep on hand must be the one that shows how far a session has advanced. The statement below is quite helpful when seeking to estimate the percentage of backup or restore processes that have been finished. SELECT Session_Id, Reads, Writes, Cpu_Time, Logical_Reads, Total_Elapsed_Time, Blocking_Session_Id, Percent_Complete, Command, (SELECT text…
Alert on Scheduled SQL Jobs – Missed to enable
During the deployments, windows patching or any other activity, DBAs often disable the jobs to avoid data corruption and jobs failure. Once the activity is completed, DBAs need to re-enable them. Since it is a manual intervention, it is possible that a job might be left behind from re-enabling. It happened in my case long…
Big Data and SQL 