Category: SQL-Tips and Tricks

SQL Server – sp_MSforeachdb – Undocumented Stored Procedure

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

The undocumented stored procedure “sp_MSforeachdb” is found in the “master” database. This is similar to sp_MSforeachtable. This stored procedure Sp_MSforeachdb gives a DBA the ability to cycle through every database in your catalog. This stored procedure will loop every database in your catalog for performing a command. This stored procedure accepts the following input parameters.   Example:  You can run a DBCC CHECKDB on all databases as below EXEC sp_Msforeachdb "DBCC checkdb ('?')" Similarly you can run other command also.

» Read more
,

SQL Server – sp_MSforeachtable – Undocumented Stored Procedure

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data. No doubt we can use the dynamic statement or cursor for this purpose. But we have better alternative way; this is an undocumented stored procedure called as “sp_MSforeachtable” in the master database. This stored procedure will loop through all the tables

» Read more
,

SQL Server – Order By Clause Based on a Variable

Guest Post, SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks

This is a guest post from one of my good friend and Developer Ranjoyt Singh. He is a dedicated and committed person professionally and personally. We developers come across many times in a situation where we need to change the sort order in which results of a query are displayed. Sorting based on a variable can be achieved in the application as well as in T-SQL. The SQL Server gives performance benefit over application. So, let’s explore what are the

» Read more
, ,

SQL Server – How to enable output logging for a replication agent

SQL, SQL-Advanced, SQL-Replication, SQL-Tips and Tricks, Varinder Sandhu

You can configure the SQL Server replication agents such as, the Snapshot Agent, Log Reader Agent, Queue Reader Agent, Distribution Agent, and Merge Agent to capture more information about error messages and procedure calls to a text file. For example, you can capture the additional information about error messages and procedure calls to troubleshoot agent failures. The following options are provided for each of the replication agents to enable logging to an output file. If the specified file name exists,

» Read more
, ,

SQL Server – List of all the Error codes or messages

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Contains one row for each system error or warning that can be returned by Microsoft SQL Server. SQL Server displays the error description on the user’s screen. You can get the List of all the Error codes or messages as USE master GO SELECT * FROM sysmessages You will get the five columns as Error – Unique error number Severity – Severity level of the error Dlevel – For internal use only Description – Explanation of the error with placeholders

» Read more
, ,

SQL SERVER – The Self Join

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

A self-join is joining a table to itself. This is done by using table name aliases to give each instance of the table a separate name. Self Join is very useful when you want to compare values in a column to other values in the same column. Self join and its example is very common question in the interview. Let us take a look to example

» Read more
, ,

SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

SQL Server database administrators frequently need in especially development and test environments to find and kill all the blocked process. Following scripts are useful in that scenario. Script – 1 — Find All the Blocked Processes SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=SUBSTRING(hostname,1, 12), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)   Script 2 — Kill all the Blocked Processes of a Database DECLARE @DatabaseName nvarchar(50) — Set the Database Name SET

» Read more
, , ,

SQL Server Denali – New Logical Functions

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

There are two new Logical functions are introduced in Denali. Let‘s have a look IFF CHOOSE IFF It Returns one of two values, depending on whether the Boolean expression evaluates to true or false. IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression

» Read more
, , ,

SQL Server – Ranking Functions – RANK, DENSE_RANK, ROW_NUMBER, NTILE

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic. There are four functions RANK DENSE_RANK ROW_NUMBER NTILE RANK   Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. DENSE_RANK Returns the rank of

» Read more
, , , , ,

Joes 2 Pros – SQL Query Techniques for Microsoft SQL Server 2008, Volume 2

SQL, SQL-Tips and Tricks, Varinder Sandhu

This book has been authored by Rick A. Morelan. If you are a SQL Developer, you must read the series of this book. Highlights of the Book The Contents of the 2th Volume of the SQL Queries Joes 2 Pros Data information and Tables Query Options Null, Expressions, and Identity Fields Aggregating Data Aggregation Strategies Top Records Ranking Functions Multiple Query Operators Common Table Expressions (CTE) Data Recursion Using Sub queries Table Data Actions The Merge Statement The Output Clause

» Read more

SQL Server 2008 – Object Explorer Details and SQL Server Object Search

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

My one friend calls me and asks where I find the Object detail in SQL Server 2008. This is very simple but may be some people are in the same situation and finding the Object Explorer Details in SQL Server 2008. That‘s why here sharing Object Explorer Details and SQL Server Object Search. SQL Server Object Search is useful for developer to find the some specific object in the database or across SQL Instance. Object Explorer Details, a component of

» Read more
, , ,

SQL Server – PARSE() and TRY_PARSE() conversion functions

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

PARSE() and TRY_PARSE() are new conversion function introduced in SQL Server Denali. TRY_PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns NULL. PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns error. Also, we can also specify the CULTURE, during this conversion.  CULTURE, means a language which will used by SQL Server to interpret data. If

» Read more
, , ,
1 … 3 4 5 6 7 8