SQL Tips by Namwar Rizvi

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.

 

January 3, 2009

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

From

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

Order by Parent_Table,Child_Table

January 2, 2009

Two quickest ways to find file information of a given database

If you want to check the name of the files and their respective sizes for the given database then following are two quick tsql scripts:

1. Use sp_helpfile

This stored procedure returns the file information for the selected database e.g.

USE AdventureWorks;

GO

EXEC sp_helpfile;

GO

It will return the resultset similar to following

sp_helpfile

By default, this view returns a column called size but this is actually the size in 8KB pages therefore, to get the exact size in KB, as returned by sp_helpfile, multiply it by 8 as mentioned in above query.

January 1, 2009

Review of SQL Server 2005 Service Pack 3

Filed under: Information,SQL Server 2005 — namwar @ 8:58 PM
Tags: , ,

Microsoft has released the third service pack for SQL Server 2005. You can download it from here.

As many of us are still using SQL Server 2005 and has not moved to SQL Server 2008 yet therefore, I felt necessary to give you a breif overview of what has been offered in third service pack for SQL Server 200. Following is an outline:

1. SP3 contains all hotfixes for SP2 along with some new features.
2. If you are not ready to implement the SP3 yet then you can apply Cummulative update 11 for SQL Sever 2005 Service Pack 2 available here
3. DBCC commands will display all error messages in SP3 in contrast to SP2 where you are only able to see first 200 messages if you do not specify ALL_ERRORMSGS option. In SP3 ALL_ERRORMSGS option does not have any meaning.
4. Notification services (which has been discontinued in SQL Server 2008) of SQL Server 2005 will work with both SQL Server 2005 and SQL Server 2008 database engine after applying SP3
5. There is a small update in Replication area i.e. a stored procedure sp_showpendingchanges now has one additional parameter @show_rows. This is a flag which allows you to specify two additional columns in the resultset for more detailed information. Further information is available here
6. There are three updates in Reporting Services area:

* Report models can be created for Terra data databases. Further information is available here
* PDF rendering extension can now embed the font to display the document correctly on a computer where document fonts are not already installed. Further information is available here
* Sharepoint integrated mode has been optimized by reducing the number of calls to Sharepoint object model. Reports will render faster now.

I hope this overview will give you a brief idea about what is new in SP3.

Happy New Year to all readers and best wishes for 2009.

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 28, 2008

Resource Governor in SQL Server 2008 – Beware of catches

Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization.  Following are some points to remember for better utilization of this feature:

  1. Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.
  2. Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.
  3. If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

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

August 7, 2008

Welcome to SQL Server 2008

Filed under: Information,SQL Server 2008 — namwar @ 10:20 PM
Tags: ,

Finally, wait is over and just one day before start of 2008 Olympic, Microsoft has released SQL Server 2008. It is a big day as many of us were waiting for the final release to start taking architectural decision based on new technologies offered by SQL Server 2008. The 180 day trial version is available here
To start with, I will recommend to watch following videos covering disfferent aspect of technologies used in SQL Server 2008
SQL Server 2008 Demos and Videos
You can also check these Web Casts

In my next articles, I will try to cover some fundamental technological advancements in this version of SQL Server 2008.

« Previous PageNext Page »

Create a free website or blog at WordPress.com.