SQL Scripts Archives - Page 285 of 318 - SQL Authority with Pinal Dave

SQL SERVER – Get Information of Index of Tables and Indexed Columns

December 18, 2007

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure. USE AdventureWorks; GO EXEC sp_helpindex 'Person.Address' GO Above SP will…

SQL SERVER – T-SQL Script to Find Details About TempDB Information

December 17, 2007

Pinal Dave

SQL Tips and Tricks

SQL Scripts, SQL Server, SQL TempDB

Two days ago I wrote an article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about Temp DB. I use following T-SQL Script to know details about my TempDB. This script is a pretty old script but it does work great most of the time. I strongly encourage all of you to use a script to check your TempDB Information.

SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use.

December 7, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts

Msg 3702, Level 16, State 3, Line 2 Cannot drop database “DataBaseName” because it is currently in use. This is a very generic error when DROP Database is command is executed and the database is not dropped. The common mistake user is kept the connection open with this database and…

SQL SERVER – UDF – Remove Duplicate Chars From String

December 5, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Duplicate Records, SQL Function, SQL Scripts, SQL String

Few days ago, I received following wonderful UDF from one of this blog reader. This UDF is written for specific purpose of removing duplicate chars string from one large string. Virendra Chauhan, author of this UDF is working as DBA in Lutheran Health Network. CREATE FUNCTION dbo.REMOVE_DUPLICATE_INSTR (@datalen_tocheck INT,@string VARCHAR(255))…

SQL SERVER – Correct Syntax for Stored Procedure SP

November 28, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

Just a day ago, I received interesting question about correct syntax for Stored Procedure. Many readers of this blog will think that it is very simple question. The reason this is interesting is the question behavior of BEGIN … END statements and GO command in Stored Procedure. Let us first…

SQL SERVER – 2005 – List All Stored Procedure in Database

November 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database. SELECT * FROM sys.procedures; This will ONLY work with SQL Server 2005. Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Generate Incremented Linear Number Sequence

November 21, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server

Just a day ago, I received interesting question on this blog. Read original question here. This is very good question and after reading this question I quickly wrote small script as answer. Let us see the question and answer together.

Q. How can we generate incremented linear number in sql server as in oracle we generate in via sequence?