SQL Tips by Namwar Rizvi

April 13, 2009

SQL Server Client Tools Setup Fails due to MSXML 6

If you are trying to install SQL Server 2005 client tools and continuously getting failure message because of MSXML 6 installation which already exists on your machine then it may be due to the corrupted installation of MSXML 6 which blocks the installation of client tools. You can fix it as follows:

Go to Control Panel-> Administrative Tools->Add Remove Programs and try to remove it. If it removes smoothly then just install the Client Tools as before and you are done. 

OR

If you found that you are unable to uninstall the MSXML 6 because of some wiered error then you need a tool called Windows Install Clean up from here

Donwload it and remove the corrupt installation of MSXML 6. Once removed, you can now install the Client tools as usual.

January 8, 2009

Search all columns of all tables for a particular value

Filed under: How To,Object Search,tips,TSQL — namwar @ 7:30 PM
Tags: , , , , ,

Several times I have came across with the question that how can I search all columns of all tables in a SQL Server database for a particular value. Unfortunately, you can not perform this query directly by any already provided system table or object. 

There are some fundamental points to remember:

 

  1. You have to iterate the list of tables and for each table you have to iterate each column and perform an exist query against it. 
  2. You will need three types of queries due to the data types i.e. string, number and date and time.
I have found an excellent article here which provides a good explanation along with the necessary sql script. I would recommend every reader to read this article.

 

November 10, 2008

Generate rows for missing dates

Filed under: SQL Server 2005,tips,TSQL — namwar @ 9:16 PM
Tags: ,

For all those reports or queries where you want to list a row for each day such that if no data is available for that day then you need zero against that date then you can use the Calendar table for the missing dates.

For generating a calendar table efficiently, you can refer to my earlier post here

Assuming you have a calendar table, use the following query to generate the required result

Use tempdb
Go

/*Create a sample table*/
drop table sampleData
go
Create table sampleData
(
    id int IDENTITY(1,1) NOT NULL,
    transactionDate datetime,
    Qty int

)

Insert into sampleData (transactionDate,Qty) values ('20081101',2)
Insert into sampleData (transactionDate,Qty) values ('20081101',3)

Insert into sampleData (transactionDate,Qty) values ('20081104',1)
Insert into sampleData (transactionDate,Qty) values ('20081104',2)
Go

Select C.CalendarDate,Isnull(A.TotalQty,0) as TotalQty
from
Calendar C
left outer join
(
    Select transactionDate,Sum(Qty) as TotalQty
    from sampleData
    group by transactionDate
) A
on C.CalendarDate=A.transactionDate
Where C.CalendarDate between '20081101' AND '20081130'

September 14, 2008

Quick technique to retrieve alphanumeric values by using regular expression in TSQL

Filed under: Query,tips,TSQL — namwar @ 12:33 AM
Tags: , ,

If you have a column in a database which contains numeric, alphanumeric and only aplha values and you just want to find all those rows which have alphanumeric values i.e which contains atleast one numeric alongwith anynumber of alphabets then you can use the power of Like operator and a little regular expression technique. Following is a query for Name column in Production.Product table of AdventureWorks database. This query retrieves all rows which contains alphanumeric values:

Use AdventureWorks

Go

Select * from Production.Product Where Name like ‘%[0-9]%’

Go

August 27, 2008

How to copy query results With column headers?

Filed under: SQL Server 2005,tips — namwar @ 9:56 PM
Tags: , ,

It is a very common practice that sometime you need to copy the query results from SQL Server Management Studio (SSMS) results tab to excel or any other application. The problem normally we face is that it does copy column headers and you have to write the column headers by your self. Here is the quick tip to enable column headers copying.

  1. In SSMS, open Tools menu and click Options
  2. Expand Query Results node and click Results to Grid
  3. Check Include column headers when copying or saving the results. Your screen will look similar to following:
  4. Results To Grid Option Screen

  5. Click Save to save your settings.

Now, whenever you will copy the query results, column headers will be copied too.

Enjoy!

August 26, 2008

Deleting ununsed primary keys

Filed under: Query,tips,TSQL — namwar @ 10:35 PM
Tags: , ,

Sometimes we need to make sure that all keys in the primary key table has atleast one corresponding row in child table or in other words every key has been used atleast once as a foreign key. This is a typical scenario we face during data import where data integrity can not be guarnteed 100% due to various reason. Following script demonstrate a simple technique to delete all those rows in the master table which are not used in child table.

Use tempdb;

/* Create sample tables and insert sample data */

Create table tblMaster

(

masterId int,

masterName varchar(50)

)

Go

Create table tblChild

(

childId int NOT NULL IDENTITY (1, 1),

masterId int,

childName varchar(50)

)

Go

Insert into tblMaster (masterId,masterName) values (1,‘Master 1’);

Insert into tblMaster (masterId,masterName) values (2,‘Master 2’);

Insert into tblMaster (masterId,masterName) values (3,‘Master 3’);

Insert into tblMaster (masterId,masterName) values (4,‘Master 4’);

Go

Insert into tblChild (masterId,childName) values (1,‘Child 1’);

Insert into tblChild (masterId,childName) values (1,‘Child 2’);

Insert into tblChild (masterId,childName) values (1,‘Child 3’);

Insert into tblChild (masterId,childName) values (2,‘Child 4’);

Go

/* Master table before deletion */

Select * from tblMaster

Go

/* Delete unused rows */

Delete tblMaster

where

not exists

(

Select masterId

from tblChild

where

tblChild.masterId=tblMaster.masterId

)

Go

/* Master table after deletion */

Select * from tblMaster

Go

/* Clean up */

drop table tblMaster;

Go

drop table tblChild;

Go

September 5, 2007

Validating Inputs and Finding Patterns with PatIndex

Filed under: string manipulation,tips,TSQL,Utility Functions — namwar @ 8:54 PM

PatIndex is one of most powerful functions of TSQL but often developers ignore it firstly because of the availability of CharIndex and secondly, the difficulty of creating patterns for PatIndex to search. Developers try to solve some typical problems faced by today’s web application by developing their own workarounds instead of using PatIndex.
Today, I will share some very useful tricks which can help you to make sure that your input string is according to the pattern you want like all numeric, all letters, starts with letter and end with number etc.

Following are the TSQL scripts which you can use to solve these type of problems. You can convert them to user defined functions as per your requirements:

—Checks that input only contains numbers

if PatIndex(‘%[^0-9]%’,’11’) > 0
Begin
Print ‘Not all numbers’
End
Else
Begin

Print ‘All numbers’
End

—Checks that input only contains letters
if PatIndex(‘%[^a-z]%’,‘aaaaa’) > 0
Begin
Print ‘Not all letters’
End
Else
Begin

Print ‘All letters’
End

–Checking for mixed input
If PatIndex(‘%[^0-9][0-9]%’,‘abc’) > 0
Begin
Print ‘Alpha numeric data’
End
Else
Begin

Print ‘Either all numbers or all letters’
End

–Checks that value must start with a letter and a number
If PatIndex(‘[^0-9][0-9]%’,‘A1anamwar11’) > 0
Begin
Print ‘Starts with a letter and a number’
End
Else
Begin

Print ‘Does not start with a letter and a number’
End

–Checks that value must End with a letter and a number
If PatIndex(‘%[^0-9][0-9]’,‘A1anamwar11a1’) > 0
Begin
Print ‘Ends with a letter and a number’
End
Else
Begin

Print ‘Does not End with a letter and a number’
End

–Checks that value must Start with a letter and Ends with a number
If PatIndex(‘[^0-9]%[0-9]’,‘namwar1’) > 0
Begin
Print ‘Starts with a letter and ends with a number’
End
Else
Begin

Print ‘Does not start with a letter and ends with a number’
End

September 3, 2007

Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’

End
Else
Begin

–Column already exists. leave the table as it is.
Print ‘Column already exists.’

End
Go

Drop table testTable
Go

August 24, 2007

Number padding in TSQL

Filed under: string concatenation,string manipulation,tips,TSQL — namwar @ 9:24 PM

Sometime we need to pad leading zeros to numeric
values so that they can appear having same width
like 00001,0022,0934 etc.
Following code demonstrates the technique
to pad leading zeros in front of digits.

–Variable to hold max length
Declare @m_maxLength int

–Create a test table
Declare @m_testTable table (sampleValue int)

–Insert some sample values
Insert into @m_testTable values (1)
Insert into @m_testTable values (2)
Insert into @m_testTable values (19)
Insert into @m_testTable values (201)
Insert into @m_testTable values (20231)

–Calcualte the length of maximum number in the table
Select
@m_maxLength=Max(Len(convert(varchar,sampleValue)))
from @m_testTable

—Now select the values
Select
sampleValue,
Replicate(‘0’,@m_maxLengthLen(convert(varchar,sampleValue)))
+
convert(varchar,sampleValue) as paddedValue

from @m_testTable

August 20, 2007

ENABLE/DISABLE all triggers of all tables in one statement

Filed under: Shortcuts,tips,Undocumented features,Utility Functions — namwar @ 8:15 PM

Sometimes we need to disable triggers to perform some tasks. Disabling the trigger by going to each table is very tedious. Following is avery quick way of disabling all triggers on all tables of the given database in a single statement.

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all triggers, you can use following statement

sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

Next Page »

Create a free website or blog at WordPress.com.