MS SQL Server 2005 – Page 5 – Ritesh Shah (Extreme-Advice.Com)

Find fully qualified path in SQL Server 2008/2005

It has been observed so many times that when programmer creates table or stored procedure in SQL Server and when tries to run it with its name, they face an error that object doesn’t exists. If you are sure that you have created object even though it is not available even after you are having full permission in database than there is a case that, object owns by particular schema and you are not referring it while calling.

There are few schema in Adventureworks database and each owns few tables. If you try to use table name only in SELECT statement, you will face an error given below.

–try running below query

use AdventureWorks

select * from Address

–it will show you below given error

–Msg 208, Level 16, State 1, Line 1

–Invalid object name ‘Address’.

–reason is Address table is owned by Person schema

–so if you try like below, you are done.

select * from Person.Address

–Person.Address

–we have specified schema name and table name in above query

–but the good practise is to specify ServerName.DatabaseName.Schemaname.ObjectName

–this is called fully qualified path and to find out fully qualified path of object

–use below give T-SQL statement.

select ‘[‘+@@SERVERNAME+‘]’ + ‘.’ +DB_NAME() + ‘.’ +ss.name+‘.’+so.name as ‘Full Qualified Name’ from sys.objects so join sys.schemas ss

on so.schema_id=ss.schema_id where so.name=‘Address’

Enjoy!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Change collation in SQL Server 2005/2008

Collation: It’s nothing but the set of rules that defined how data is stored and compared.

SQL Server default uses collation “SQL_Latin1_General_CP1_CI_AS” which is:

–The ISO code page 1252.

–The dictionary order, case-insensitive character sort order.

–The General Unicode collation

If you want to change this default collation to your own regional collation for your database, you can use below given script.

Alter Database AdventureWorks –give your database name

Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply

You may face the error while running above script sometime if your database is in multi user mode, in that case you have to set your database to single user mode, change collation and set your database to multi user mode. Look at the script below.

Alter Database AdventureWOrks set single_user

Alter Database AdventureWorks –give your database name

Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply

Alter Database AdventureWOrks set multi_user

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Table statistics in SQL Server 2005/2008/2000 like Table size, total row, index size etc.

We may often need to see total row in each table, data size, index size, used and unused space in table etc. Well, in this case SP_SpaceUsed stored procedure could be used but it shows you states of only one table, what if we wants this type of statistics for all use tables in entire database? In this case, undocumented stored procedure “SP_MsForEachTable” will come to our help. Let us see it practically.

–create SP which will display states of all tables

Create proc tableState

–create temporary table

–which will store all states

CREATE TABLE #TableState (

tableName sysname ,

rowCounts INT,

reservedSize VARCHAR(50),

dataSize VARCHAR(50),

indexSize VARCHAR(50),

unusedSize VARCHAR(50))

SET NOCOUNT ON

–insert result of SP_SpaceUsed in temp table

–if you go for CURSOR or LOOP you don’t need to use

–sp_msforeachtable stored procedure

–but to avoid CURSOR,sp_msforeachtable could be good alternative

INSERT #TableState

EXEC sp_msforeachtable ‘sp_spaceused ”?”’

–display data of temp table

SELECT * FROM #TableState order by tableName

–drop temp table explicitly!!

DROP TABLE #TableState

–run above SP

exec tableState

Happy Programming!!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update NULL records in all columns with any value which is NOT NULL in same column SQL Server 2005/2008

Today I gave solution for one strange problem in one of the forum; I thought to share that script with all of you. Requirement was something like below:

— Update all field of Table which is NULL

–NULL data should be populated with NOT NULL value of the same column

Well, this is somehow strange but it was needed so I quickly create one small script with the help of cursor, however, I always avoid cursor as long as possible. I didn’t find any other quick solution at that time.

–create table for demo

if OBJECT_ID(’emps’,‘U’) is not null drop table emps

CREATE TABLE [dbo].[emps](

[Name] [varchar](50) NULL,

[Dept] [varchar](10) NULL,

[Company] [varchar](15) NULL

) ON [PRIMARY]

–insert some data

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘CHEM’ UNION ALL

SELECT ‘RAJAN’,NULL,NULL UNION ALL

SELECT NULL,‘ACCT’,‘MAR’

–script with cursor

declare @SQL nvarchar(max)

DECLARE @ColName VARCHaR(15)

set @SQL=”

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=’emps’

OPEN FirstCur

FETCH FROM FirstCur INTO @ColName

WHILE @@FETCH_STATUS=0

BEGIN

SET @SQL=@SQL+ ‘ Update Emps SET ‘ + @ColName + ‘ = (SELECT top 1 ‘ + @ColName + ‘ FROM emps where ‘ + @ColName + ‘ is not null) where ‘ + @ColName + ‘ is null; ‘

FETCH NEXT FROM FirstCur INTO @ColName

END

print @sql

CLOSE FirstCur

DEALLOCATE FirstCur

exec sp_executeSQL @SQL

–CHECK DATA

select * from emps

Happy Coding!!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

List SQL Server Agent JOBS in SQL Server 2008/2005/2000

As a DBA we may need to set the JOB for various task and those tasks will be saved under MSDB database. You can see it from EM or from SSMS GUI tools but it would help sometime to execute query to see the list of available JOB in SQL Server 2000+ versions.

There are two ways to achieve that task. Have a look at it.

–with stored procedure

EXEC MSDB..sp_HELP_JOB

–with sysjobs view

select * from msdb..sysjobs

Both of the above statements will give you list of JOB have been set in SQL Server Agent along with so many other important details.

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use of GROUPING SETS in SQL Server 2008 which could be replacement of CUBE and ROLLUP

I have used CUBE and ROLLUP in my last article at below given link:

http://www.sqlhub.com/2009/05/use-of-cube-and-rollup-difference-of.html

Those are really handy and very useful tools, especially for reporting purpose. SQL Server 2008 came up with more powerful utility called GROUPING SETS. GROUPING SETS is more user friendly and easy to use as compare with CUBE and ROLL UP. I will use same table and data I have used in my CUBE and ROLLUP article so that everybody can compare the changes.

Have a look at it.

–Table 1 for Demo

if object_id(‘dbo.orders’,‘U’) is not null drop table dbo.orders

create table dbo.Orders

(

OrderID varchar(5),

OrderDate varchar(50)

)

–date for table1

insert into dbo.Orders

select ‘A1000’,GETDATE()-1 union all

select ‘A1001’,GETDATE()

–table 2 for demo

if object_id(‘dbo.OrderDetails’,‘U’) is not null drop table dbo.orderDetails

create table dbo.OrderDetails

(

OrderID varchar(5),

SampleNo Varchar(8),

SampleDate varchar(50)

)

–data for table 2

insert into dbo.OrderDetails

select ‘A1000’,‘A1000-01’,GETDATE()-1 union all

select ‘A1000’,‘A1000-02’,GETDATE() union all

select ‘A1000’,‘A1000-03’,GETDATE() union all

select ‘A1001’,‘A1001-01’,GETDATE() union all

select ‘A1001’,‘A1001-02’,GETDATE()

–1.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

–2.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by grouping sets ((o.OrderID,SampleDate))

/******************************************************************

there is not difference between query # 1 and 2

both will shows you results

OrderID SampleDate Total Sample

——- ————————————————– ————

A1000 May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1001 May 26 2009 1:56PM 2

(3 row(s) affected)

OrderID SampleDate Total Sample

——- ————————————————– ————

A1000 May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1001 May 26 2009 1:56PM 2

(3 row(s) affected)

*******************************************************************/

–now we will start real journey of Grouping Sets

–3.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by grouping sets ((o.OrderID,SampleDate),(o.OrderID))

/******************************************************************

above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID

You can see we are getting total sample of A1000 and A1001

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1000 Total Sample for OrdID 3

A1001 May 26 2009 1:56PM 2

A1001 Total Sample for OrdID 2

(5 row(s) affected)

*******************************************************************/

–now we need total on date rather than OrderID, let us see how it comes with below query.

–4.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by grouping sets ((o.OrderID,SampleDate),(SampleDate))

/******************************************************************

above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID

You can see we are getting total sample of A1000 and A1001

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 25 2009 1:56PM 1

Total Sample on Date May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1001 May 26 2009 1:56PM 2

Total Sample on Date May 26 2009 1:56PM 4

(5 row(s) affected)

*******************************************************************/

–now we need total on date and total by OrderID, let us see how it comes with below query.

–5.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by grouping sets ((o.OrderID,SampleDate),(SampleDate),(o.OrderID))

/******************************************************************

above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID

and another aggregation will be applied to date also.

You can see we are getting total sample of A1000 and A1001 and total sample came on

25th and 26th May 2009

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 25 2009 1:56PM 1

Total Sample on Date May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1001 May 26 2009 1:56PM 2

Total Sample on Date May 26 2009 1:56PM 4

A1000 Total Sample for OrdID 3

A1001 Total Sample for OrdID 2

(7 row(s) affected)

*******************************************************************/

–now, below query is exactly same like Query # 5 the only difference is

–we need grand total

–6.)

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by grouping sets ((o.OrderID,SampleDate),(SampleDate),(o.OrderID),())

/******************************************************************

above query will make group by on OrderID and SampleDate, aggregation will be applied to OrderID

and another aggregation will be applied to date also, apart from that () with no group by denots that

we need grand total also.

You can see we are getting total sample of A1000 and A1001 and total sample came on

25th and 26th May 2009 along with Total sample for all orderID so far, which is 5, which

is on 6th row.

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 25 2009 1:56PM 1

Total Sample on Date May 25 2009 1:56PM 1

A1000 May 26 2009 1:56PM 2

A1001 May 26 2009 1:56PM 2

Total Sample on Date May 26 2009 1:56PM 4

Total Sample on Date Total Sample for OrdID 5

A1000 Total Sample for OrdID 3

A1001 Total Sample for OrdID 2

(8 row(s) affected)

*******************************************************************/

Happy Coding!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use of CUBE and ROLLUP – Difference of CUBE and ROLLUP in SQL Server 2005/2008

SQL Server 2005+ came up with exciting facility of CUBE and ROLLUP clause. Herewith, I am going to show you use and difference of CUBE and ROLLUP in SQL Server 2005 and SQL Server 2008.

CUBE: generates a result set that represents aggregates for all combinations of values in the selected columns

ROLLUP: generates a result set that represents aggregates for a hierarchy of values in the selected columns

Let us see one practical scenario which will make your concept much clear about both these exciting features.

–Table 1 for Demo

create table dbo.Orders

(

OrderID varchar(5),

OrderDate varchar(50)

)

–date for table1

insert into dbo.Orders

select ‘A1000’,GETDATE()-1 union all

select ‘A1001’,GETDATE()

–table 2 for demo

create table dbo.OrderDetails

(

OrderID varchar(5),

SampleNo Varchar(8),

SampleDate varchar(50)

)

–data for table 2

insert into dbo.OrderDetails

select ‘A1000’,‘A1000-01’,GETDATE()-1 union all

select ‘A1000’,‘A1000-02’,GETDATE() union all

select ‘A1000’,‘A1000-03’,GETDATE() union all

select ‘A1001’,‘A1001-01’,GETDATE() union all

select ‘A1001’,‘A1001-02’,GETDATE()

–let us check both table

select * from dbo.Orders

select * from dbo.OrderDetails

–let us check how many samples came for each order id

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000 3

A1001 2

(2 row(s) affected)

*************************************************************/

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with cube

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000 3

A1001 2

NULL 5

(3 row(s) affected)

*************************************************************/

select o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with rollup

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000 3

A1001 2

NULL 5

(3 row(s) affected)

*************************************************************/

–You will not be able to find big difference between ROLLUP and CUBE with above query.

–isn’t there any difference at all? NO, there is a difference. let us see the difference.

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with cube

/************************************************************

answer would be.

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 24 2009 3:53PM 1

Total Sample on Date May 24 2009 3:53PM 1

A1000 May 25 2009 3:53PM 2

A1001 May 25 2009 3:53PM 2

Total Sample on Date May 25 2009 3:53PM 4

Total Sample on Date Total Sample for OrdID 5

A1000 Total Sample for OrdID 3

A1001 Total Sample for OrdID 2

(8 row(s) affected)

*************************************************************/

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with rollup

/************************************************************

answer would be.

OrderID SampleDate Total Sample

——————– ————————————————– ————

A1000 May 24 2009 3:53PM 1

A1000 May 25 2009 3:53PM 2

A1000 Total Sample for OrdID 3

A1001 May 25 2009 3:53PM 2

A1001 Total Sample for OrdID 2

Total Sample on Date Total Sample for OrdID 5

(6 row(s) affected)

*************************************************************/

In last two queries, you find the difference between CUBE and ROLLUP. Second from last query with CUBE shows you 8 rows while last query which is using ROLLUP, showing 6 rows. CUBE will show you sample received for each orderID on each day and finally it will show you total sample received whereas ROLLUP will show you bit less summary like: which day for which ORDERID, how many samples come in. and finally total sample received.

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Check whether FileExists in SQL Server 2005/2008

Well, you might have done programming in any of the .NET flavor to check whether file is exist in particular folder or not. You might have used many classis of SYSTEM.IO namespaces and big code to check but If I tell you it is more than easy to check the file whether it is exists or not, in SQL Server than .NET, what would be your reaction? Ohh!! What are you talking about!!!!

I am not kidding, it is really very very easy, just one simple statement, no namespace, no use of class etc. etc. Have a look at it.

exec master.dbo.xp_fileexist‘d:\emps.txt’

If emps.txt would be exists in your “D” drive, you would get answer “1” in “File Exist” column or “0”. Isn’t it very easy?

Now think if you are doing some kind of T-SQL Programming and you need this value in variable, again very easy and small code snippet required for that, have a look at it again.

DECLARE @isExists INT

exec master.dbo.xp_fileexist‘d:\emps.txt’, @isExists OUTPUT

SELECT case @isExists when 1 then ‘Yes’ else ‘No’ end as isExists

Happy T-SQLing!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Shrink log file for all database in SQL Server 2008/2005

Well, shrinking log file manually and frequently is not at all good idea but we have to do it once in a while when it reaches at very big size, however if it is growing unacceptably and frequently, you have to investigate the reason before shrinking it.

Anyway, our main focus today is how to shrink log file. Well, there is a simple command “DBCC SHRINKFILE” in T-SQL but what, if we want to shrink log file of all database in SQL Server? Well in this situation SP_MsForEachDB (undocumented SP) will come to your help.

Let us see small yet handy script to do so:

use master

DECLARE @SQL varchar (2000)

SELECT @SQL = ”

SELECT @SQL = @SQL + ‘if ”?” <> ”master” and ”?” <> ”msdb” and ”?”<>”tempdb” and ”?” <> ”model” ‘

SELECT @SQL = @SQL + ‘BEGIN ‘

SELECT @SQL = @SQL + ‘USE ?; ‘

SELECT @SQL = @SQL + ‘SELECT ”?”; ‘

SELECT @SQL = @SQL + ‘DECLARE @LogFile varchar (30); ‘

SELECT @SQL = @SQL + ‘SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0x40) <> 0; ‘

SELECT @SQL = @SQL + ‘dbcc shrinkfile (@LogFile, 1,truncateonly); ‘

SELECT @SQL = @SQL + ‘SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; ‘

SELECT @SQL = @SQL + ‘END’

SELECT @SQL

EXEC sp_MSforeachdb @command1=@SQL

To know more about SP_MsForEachDB stored procedure, have a look at one of my previous article at:

http://www.sqlhub.com/2009/03/spmsforeachdb-undocumented-stored.html

http://www.sqlhub.com/2009/04/find-user-in-all-database-with.html

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah