May 14, 2010 at 6:29 am
I'm probably being really stupid here, but I'm having difficulty getting the result I want.
The part of my query that I can't get working should display data as below: -
NumberOfColumns |NumberOfRows
--------------- |------------
3 |404
3 |404
Basically, it shows the number of rows and number of columns in a specified table.
All I've been able to do is get two seperate queries to show the result like: -
NumberOfColumns
---------------
3
3
(2 row(s) affected)
NumberOfRows
------------
404
404
(2 row(s) affected)
This is what I've got so far: -
DECLARE @TABLENAME VARCHAR(100),
@SQL VARCHAR(1000)
SELECT @TABLENAME = 'Commissioner'
SELECT @SQL = 'SELECT COUNT(1) AS NumberOfRows FROM REFERENCE.[dbo].[' + @TABLENAME + ']' + ' UNION ALL ' + 'SELECT COUNT(1) FROM DEV_REFERENCE.[dbo].[' + @TABLENAME + ']'
SELECT COUNT(1) AS numberofcolumns
FROM reference.sys.syscolumns
WHERE id = (SELECT id
FROM reference.sys.sysobjects
WHERE name = '' + @TABLENAME + '')
UNION ALL
SELECT COUNT(1) AS numberofcolumns
FROM dev_reference.sys.syscolumns
WHERE id = (SELECT id
FROM dev_reference.sys.sysobjects
WHERE name = '' + @TABLENAME + '')
EXECUTE(@SQL)
What I could do with is joining the two resulting sets in a single query. Have I made any sense in this rambling? :hehe:
May 14, 2010 at 6:34 am
skcadavre (5/14/2010)
All I've been able to do is get two seperate queries
One way to do it reusing your code? How about derived tables? use your two queries as derived tables in an outer query. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2010 at 6:53 am
PaulB-TheOneAndOnly (5/14/2010)
skcadavre (5/14/2010)
All I've been able to do is get two seperate queriesOne way to do it reusing your code? How about derived tables? use your two queries as derived tables in an outer query. 😉
I've had a bit of a play. . . not having used (or heard of!) derived tables (thanks for the mention, a new topic for me to research), so far I've failed to get anything going.
Could you give an example?
DECLARE @testtable1 TABLE(
id INT IDENTITY(1, 1),
name VARCHAR(20) PRIMARY KEY CLUSTERED)
DECLARE @testtable2 TABLE(
id INT IDENTITY(1, 1),
name VARCHAR(20) PRIMARY KEY CLUSTERED)
INSERT INTO @testtable1
(name)VALUES ('1')
INSERT INTO @testtable1
(name)VALUES ('2')
INSERT INTO @testtable1
(name)VALUES ('3')
INSERT INTO @testtable1
(name)VALUES ('4')
INSERT INTO @testtable2
(name)VALUES ('1')
INSERT INTO @testtable2
(name)VALUES ('2')
INSERT INTO @testtable2
(name)VALUES ('3')
SELECT *
FROM (SELECT COUNT(1) AS NumberOfRows
FROM @testtable1
UNION ALL
SELECT COUNT(1)
FROM @testtable2) derived_table
That would give me: -
NumberOfRows
------------
4
3
(2 row(s) affected)
I would want the "Number of Columns" to read 2 for each.
May 14, 2010 at 7:59 am
You can do it all with dynamic SQL:
DECLARE @SQL varchar(8000)
,@CRLF char(2)
,@tab char(1)
,@TABLENAME sysname
SELECT @CRLF = CHAR(13) + CHAR(10)
,@tab = CHAR(9)
,@TABLENAME = 'Commissioner'
,@SQL =
'SELECT ' + @TABLENAME + ' AS TableName' + @CRLF
+ @tab + ',R.DB' + @CRLF
+ @tab + ',R.NumRows' + @CRLF
+ @tab + ',C.NumCols' + @CRLF
+ 'FROM' + @CRLF
+ '(' + @CRLF
+ @tab + 'SELECT ''Reference'', COUNT(*)' + @CRLF
+ @tab + 'FROM REFERENCE.dbo.[' + @TableName + ']' + @CRLF
+ @tab + 'UNION ALL' + @CRLF
+ @tab + 'SELECT ''Dev_Reference'', COUNT(*)' + @CRLF
+ @tab + 'FROM DEV_REFERENCE.dbo.[' + @TableName + ']' + @CRLF
+ ') R (DB, NumRows)' + @CRLF
+ @tab + 'JOIN' + @CRLF
+ @tab + '(' + @CRLF
+ @tab + @tab + 'SELECT ''Reference'', COUNT(*)' + @CRLF
+ @tab + @tab + 'FROM reference.sys.syscolumns' + @CRLF
+ @tab + @tab + 'WHERE id = (SELECT id FROM REFERENCE.sys.sysobjects WHERE [name] = ''' + @TABLENAME + ''')' + @CRLF
+ @tab + @tab + 'UNION ALL' + @CRLF
+ @tab + @tab + 'SELECT ''Dev_Reference'', COUNT(*)' + @CRLF
+ @tab + @tab + 'FROM reference.sys.syscolumns' + @CRLF
+ @tab + @tab + 'WHERE id = (SELECT id FROM DEV_REFERENCE.sys.sysobjects WHERE [name] = ''' + @TABLENAME + ''')' + @CRLF
+ @tab + ') C (DB, NumCols)' + @CRLF
+ @tab + @tab + 'ON R.DB = C.DB' + @CRLF
--PRINT(@SQL)
EXEC(@SQL)
The derived tables here are R and C.
May 14, 2010 at 8:47 am
sure, derived tables are a little funny thing - take a look here... http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 19, 2010 at 7:13 am
A web search query is a query that a user enters into web search engine to satisfy his or her information needs. Web search queries are distinctive in that they are unstructured and often ambiguous; they vary greatly from standard query languages which are governed by strict syntax rules.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply