March 13, 2010 at 11:10 pm
Hi there,
This bit of code is from the book 'SQL server 200 for dummies'. It contains an example of a cursor in it and looping through all tables in a database to obtain a count of all rows. I understand it however these 2 lines of code are very confusing. How do they work? I know they are concatenating a text object but why are there 3 ''' around the @Tablename part. I understand the from bit its a lot simpler. Also if I want to loop across databases in this. I know I would add @DatabaseName as a local variable in the declare section above but how would this look in the select statement? I also know exec sp_executesql is a lot simpler to use for this. thanks, any help would be awesome!
set @scmd=''
set @scmd=@scmd+'select ''' + @Tablename+''',count(*)'
set @scmd=@scmd+'from AdventureWorksDW'+'.'+'dbo'+'.' + @Tablename
cheers heaps for this
Craig
Here is the entire code:
*****************************
declare @Tablename varchar(50)
declare @scmd varchar(100)
declare curtable cursor for
select Name from AdventureworksDW.sys.objects
where type='U'
order by name
OPEN curtable
FETCH NEXT FROM curtable
INTO @Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @scmd=''
set @scmd=@scmd+'select ''' + @Tablename+''',count(*)'
set @scmd=@scmd+'from AdventureWorksDW'+'.'+'dbo'+'.' + @Tablename
exec (@scmd)
FETCH NEXT FROM curtable
INTO @Tablename
END
CLOSE curtable
DEALLOCATE curtable
March 13, 2010 at 11:30 pm
To embed a single quote mark (') in a string, it needs to be doubled-up.
You can use PRINT @scmd to see how the string looks before it gets executed.
The following demonstrates three methods to achieve the same thing:
DECLARE @scmd VARCHAR(100);
DECLARE @TableName VARCHAR(50);
SET @TableName = 'DatabaseLog';
-- By doubling-up the quote characters
SET @scmd = '';
SET @scmd = @scmd + 'SELECT ''' + @Tablename + ''', COUNT(*) ';
SET @scmd = @scmd + 'FROM AdventureWorksDW' + '.' + 'dbo' + '.' + @Tablename;
PRINT @scmd;
EXECUTE (@scmd);
-- By using CHAR(39) - 39 happens to be the ASCII code for the single quote mark
SET @scmd = '';
SET @scmd = @scmd + 'SELECT ' + CHAR(39) + @Tablename + CHAR(39) + ', COUNT(*) ';
SET @scmd = @scmd + 'FROM AdventureWorksDW' + '.' + 'dbo' + '.' + @Tablename;
PRINT @scmd;
EXECUTE (@scmd);
-- By using the QUOTENAME function
SET @scmd = '';
SET @scmd = @scmd + 'SELECT ' + QUOTENAME(@Tablename, NCHAR(39)) + ', COUNT(*) ';
SET @scmd = @scmd + 'FROM AdventureWorksDW' + '.' + 'dbo' + '.' + @Tablename;
PRINT @scmd;
EXECUTE (@scmd);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 10:04 pm
craig 84462 (3/13/2010)
thanks, any help would be awesome!
Then, avoid the RBAR...
I don't know if this works in 2k8 because I don't have it to test on and I don't know if the legacy views are actually available anymore, but it works just fine in 2k5 (and 2k if you do what the comments say)...
/**********************************************************************************************************************
Script File Name: sp_SpaceUsedOnSteroids.sql
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up to snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply