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
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]
GO
–insert some data
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘CHEM’ UNION ALL
SELECT ‘RAJAN’,NULL,NULL UNION ALL
SELECT NULL,‘ACCT’,‘MAR’
GO
–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
go
–CHECK DATA
select * from emps
Happy Coding!!!!
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
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
GO
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
GO
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!!!
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
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
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
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.
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
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
GO
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
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