How to and where to use inbuilt objects in SQL server

There are multiple options to get database table and index details. Here I explain some useful objects and their use. sp_help Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-ver15 sp_helpindex Reports information about the indexes on a table or view. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpindex-transact-sql?view=sql-server-ver15 sp_helpconstraint …

How to and where to use inbuilt objects in SQL server Read More »

How to get all database-level information using T-SQL

How to get database information using system tables. Here is the sample with flowing fields: database_id : Database Id dbName : Database name Status : database state like 0 DataFiles : count of data files 1 = RESTORING 2 = RECOVERING | SQL Server 2008 and later 3 = RECOVERY_PENDING | SQL Server 2008 and …

How to get all database-level information using T-SQL Read More »

How to find unsaved file location for SQL query in management studio

This is important if we not saved files on disk and SQL management studio close. Un save file location C:\Documents and Settings\<user name>\My Documents\SQL Server Management Studio\Backup Files\Windows Vista/7/10 %USERPROFILE%\Documents\SQL Server Management Studio\Backup Files OR %USERPROFILE%\AppData\Local\Temp Sql Query find using statics Query Recovery Settings There is a setting in Management Studio that allows the Autorecover …

How to find unsaved file location for SQL query in management studio Read More »

How to find SQL Server Job history

This topic describes how to view the Microsoft SQL Server Agent job history log using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects. To view the job history log In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand SQL Server Agent, and then …

How to find SQL Server Job history Read More »

T-SQL function

What are function available in SQL Server SQL Server supports these analytic functions: CUME_DIST (Transact-SQL) LEAD (Transact-SQL) FIRST_VALUE (Transact-SQL) PERCENTILE_CONT (Transact-SQL) LAG (Transact-SQL) PERCENTILE_DISC (Transact-SQL) LAST_VALUE (Transact-SQL) PERCENT_RANK (Transact-SQL) Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use …

T-SQL function Read More »