Feed on
Posts
Comments

Archive for the ‘SQL Scripts’ Category

SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 22, 2008 | No Comments »

I love active participation from my readers. Just a day ago I wrote article about SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database. I just received comment from Jerry Hung who have improved on previously written article of generating text of Stored Procedure.
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT [...]

Read Full Post »

SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database

Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 20, 2008 | 4 Comments »

SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.
USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name
FROM sysobjects s
WHERE type = ‘P’
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = [...]

Read Full Post »

SQL SERVER - Delete Backup History - Cleanup Backup History

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 11, 2008 | 3 Comments »

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.
USE msdb
GO
DECLARE [...]

Read Full Post »

SQL SERVER - Check Database Integrity for All Databases of Server

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology on November 10, 2008 | No Comments »

Today we will see quick script which will check integrity of all the database of SQL Server.
EXEC sp_msforeachdb ‘DBCC CHECKDB(”?”)’
Above script will return you lots of messages in resultset. If there are any errors in resultset they will be displayed in red text. If everything is black text there is no error. Typical output of [...]

Read Full Post »

SQL SERVER - Refresh Database Using T-SQL

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 7, 2008 | 2 Comments »

Yesterday I received following questions on blog. Ashish Agarwal asked following question.
Hi Pinal,
Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?
Thanks,
Ashish Agarwal
Answer to above question is NO. It is not possible to do [...]

Read Full Post »

SQL SERVER - Clear Drop Down List of Recent Connection From SQL Server Management Studio

Posted in Author Pinal, Best Practices, DBA, Database, SQL, SQL Add-On, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 5, 2008 | 1 Comment »

Quite often it happens that SQL Server Management Studio’s Dropdown box is cluttered with many different SQL Server’s name. Sometime it contains the name of the server which does not exist or developer does not have access to it. It is very easy to clean the list and start over.

Delete mru.dat file from following location.
For [...]

Read Full Post »

SQL SERVER - Fix : Error: 4064 - Cannot open user default database. Login failed. Login failed for user

Posted in Author Pinal, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 4, 2008 | 1 Comment »

I have received following question nearly 10 times in last week though emails. Many users have received following error while connecting to the database. This error happens when database is dropped for which is default for some of the database user. When user try to login and their default database is dropped following error shows [...]

Read Full Post »

SQL SERVER - Stored Procedure WITH ENCRYPTION and Execution Plan

Posted in Author Pinal, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 1, 2008 | 3 Comments »

Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP [...]

Read Full Post »

SQL SERVER - DECLARE Multiple Variables in One Statement

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 31, 2008 | 2 Comments »

Just a day ago, while I was enjoying mini vacation during festival of Diwali I met one of the .NET developer who is big fan of Oracle. While discussing he suggested that he wished SQL Server should have feature where multiple variable can be declared in one statement. I requested him to not judge wonderful [...]

Read Full Post »

SQL SERVER - Simulate INNER JOIN using LEFT JOIN statement - Performance Analysis

Posted in Author Pinal, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 25, 2008 | 4 Comments »

Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it can not be used and simulating INNER JOIN using any other JOINs will degrade the [...]

Read Full Post »

SQL SERVER - Transaction and Local Variables - Swap Variables - Update All At Once Concept

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 20, 2008 | 3 Comments »

This article is inspired from two sources.
1) My year old article - SQL SERVER - Effect of TRANSACTION on Local Variable - After ROLLBACK and After COMMIT
2) Discussion with SQL Server MVP - Jacob Sebastian - SQLAuthority News - Author Visit - SQL Hour at Patni Computer Systems
I usually summarize my article at the end, [...]

Read Full Post »

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, Software Development, T SQL, Technology, tagged on October 19, 2008 | 4 Comments »

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by [...]

Read Full Post »

SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 18, 2008 | 1 Comment »

A year ago I wrote post about SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice where I have discussed two different methods of getting datepart from datetime.
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I have summarized my post suggesting that either method works fine and I prefer to [...]

Read Full Post »

SQL SERVER - Get Common Records From Two Tables Without Using Join

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 17, 2008 | 6 Comments »

I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.
Sachin Asked:
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From [...]

Read Full Post »

SQL SERVER - Introduction and Example of UNION and UNION ALL

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 15, 2008 | 3 Comments »

It is very much interesting when I get request from blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER - Union vs. Union All - Which is better for performance? with examples. I request you to read my previous article first to understand what is the [...]

Read Full Post »

SQL SERVER - Get Numeric Value From Alpha Numeric String - UDF for Get Numeric Numbers Only

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 14, 2008 | 4 Comments »

SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ” )
SET [...]

Read Full Post »

SQL SERVER - 2008 - Enhenced TRIM() Function - Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on October 10, 2008 | 5 Comments »

After reading my article SQL SERVER - 2008 - TRIM() Function - User Defined Function, I have received email and comments where user are asking if it is possible to remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.
I found following script posted by Russ and Erik. It is modified a [...]

Read Full Post »

SQL SERVER - 2008 - TRIM() Function - User Defined Function

Posted in Author Pinal, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 9, 2008 | 4 Comments »

I just received following question in email by James Louren.
“How come SQL Server 2000, 2005 does not have function TRIM()? Is there any way to get similar results.
What about SQL Server 2008?”
James has asked very interesting question. I have previously wrote about SQL SERVER - TRIM() Function - UDF TRIM(). Today my answer is no [...]

Read Full Post »

SQL SERVER - 2008 - Find If Index is Being Used in Database

Posted in Author Pinal, SQL, SQL Authority, SQL DMV, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 3, 2008 | 5 Comments »

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase [...]

Read Full Post »

SQL Server - 2008 - Cheat Sheet - One Page PDF Download

Posted in Author Pinal, Best Practices, DBA, Database, SQL, SQL Add-On, SQL Authority, SQL Coding Standards, SQL Documentation, SQL Download, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, Software Development, T SQL, Technology on October 2, 2008 | 8 Comments »

Very frequently I have been asked to create a page, post or article where in one page all the important concepts of SQL Server are covered. SQL Server 2008 is very large subject and can not be even covered 1000 of pages. In daily life of DBA there are few commands very frequently used and [...]

Read Full Post »

Older Posts »