Query

  • 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:


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.
  • PaulB-TheOneAndOnly (5/14/2010)


    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. 😉

    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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.

  • 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.
  • 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.

    Land Rover Range Rover Parts

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply