October 18, 2013 at 7:26 am
Hello,
Difficult to explain but I have added all raw data and what I want. Hopefully somebody can clarif my issue, or why it does not work correct
I have a table which contains table sizes of all tables in a database.
But now I want to have all summed up per application (an application name is the beginning of a table) but you can see it in the raw data added.
The query I run works fine, but when it encounters a similar name in the beginning it counts up all the other tables also.
The needs to be in a database again if you want to test.
The script I created is
--------
Declare @appnaam varchar(30);
Declare @appnaam2 varchar(30);
Declare @Rnum integer;
Declare @maxnum integer;
Set @Rnum = 1;
Select @maxnum = COUNT(*) from tablesizetestname
Set @Maxnum=@Maxnum+1
WHILE @RNUM<@Maxnum
BEGIN
WITH Naam AS
(
SELECT ROW_NUMBER() OVER(ORDER BY APPNAME) as RowNum, APPNAME
FROM tablesizetestname
)
SELECT @appnaam = appname FROM Naam where rownum=@RNUM
SET @appnaam2=@appnaam +'_%'
SELECT @appnaam AS application,
@appnaam2 AS zoeknaam,
SUM(Rowscount) as TotalRows,
SUM(Reserved) as Reserved,
SUM(Datasize) as Data,
SUM(indexsize) as Index_Size,
SUM(unused) as Unused
FROM tablesizeFRC where Name like @appnaam2
SET @Rnum=@rnum+1
END
SELECT 'HFM_ERRORLOG' AS application,
SUM(Rowscount) as TotalRows,
SUM(Reserved) as Reserved,
SUM(Datasize) as Data,
SUM(indexsize) as Index_Size,
SUM(unused) as Unused
FROM tablesizeFRC where Name like 'HFM_ERRORLOG%'
-------------
and the table info from tablesizetestname is
appname
--------------------------------------------------
FACT
Simple
Fact11
Fact10
Factarch
factbk
factreb
fact12
The result is the following, but if you see the first line with FACT it is the total of all tablecounts with FACT in it, but all the others are fine.
Why does my _% does not work in the like statement
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
FACT FACT_% 1626930850 428223 294882 131637 79
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Fact10 Fact10_% 194820963 46022 32777 13090 0
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Fact11 Fact11_% 281578325 66253 47122 18738 0
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
fact12 fact12_% 545360834 131757 92271 38944 0
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Factarch Factarch_% 254219651 59988 42478 17103 0
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
factbk factbk_% 168098831 42147 28449 13637 11
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
factreb factreb_% NULL NULL NULL NULL NULL
(1 row(s) affected)
application zoeknaam TotalRows Reserved Data Index_Size Unused
------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Simple Simple_% 2484 0 0 0 0
(1 row(s) affected)
application TotalRows Reserved Data Index_Size Unused
------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
HFM_ERRORLOG 807387 180 176 2 1
October 18, 2013 at 12:22 pm
Hm. From what I can tell, you're trying to search for something LIKE '_%', correct? That is, you want to search for things where there's an underscore, then any characters afterwards?
If so, you need brackets around the underscore, so...
SET @appnaam2=@appnaam +'_%'
...
FROM tablesizeFRC where Name like @appnaam2
That won't work; instead, add the wildcard like so:
SET @appnaam2=@appnaam +'[_]%'
...
FROM tablesizeFRC where Name like @appnaam2
That should fix things up.
- 😀
October 20, 2013 at 11:31 pm
You need to use squar brackets with underscore like this:
SET @appnaam2=@appnaam +'[_]%'
It will works
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 29, 2013 at 6:07 am
Thanks, that was the solution for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply