Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2013
    Posts
    17

    Unanswered: Naming query column the name of resulting Table name from query

    Hi newbie here, first let me say this I am not asking you to do my homework..So please don't make that statement. I have spent near 11 hours trying to figure this one thing out and can't. Basically I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..

    Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
    Hint: Use the sys.tables catalog view.

    I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example.. Thank you.

  2. #2
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    For the sake of learning, I would break this down into parts. First, can you write a query to determine the table that comes first alphabetically?

  3. #3
    Join Date
    Oct 2013
    Posts
    17

    yes

    Quote Originally Posted by clawlan View Post
    For the sake of learning, I would break this down into parts. First, can you write a query to determine the table that comes first alphabetically?
    yes this is the code
    Code:
    SELECT name 
    FROM sys.tables
    WHERE name = (select Top 1 name from sys.tables)
    order by name
    however I also have a problem of getting a count of the rows in the table from the result of the query.

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by dariusd7 View Post
    yes this is the code
    Code:
    SELECT name 
    FROM sys.tables
    WHERE name = (select Top 1 name from sys.tables)
    order by name
    however I also have a problem of getting a count of the rows in the table from the result of the query.
    You sort of did that in a round about way. Why not just put the "top 1" into the original select statement? You also did not take into consideration "it should exclude tables named dtproperties and sysdiagrams".

    EDIT: We will get to the count next. I just want to help walk you through the thought process with the hopes it will help you learn.
    Last edited by clawlan; 10-23-14 at 18:47.

  5. #5
    Join Date
    Oct 2013
    Posts
    17
    Quote Originally Posted by clawlan View Post
    You sort of did that in a round about way. Why not just put the "top 1" into the original select statement? You also did not take into consideration "it should exclude tables named dtproperties and sysdiagrams".

    EDIT: We will get to the count next. I just want to help walk you through the thought process with the hopes it will help you learn.

    USE AP

    Declare @MyRowCount int

    SELECT Top 1 name
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc

    SET @MyRowCount = @@ROWCOUNT
    ;

  6. #6
    Join Date
    Oct 2013
    Posts
    17
    Quote Originally Posted by clawlan View Post
    You sort of did that in a round about way. Why not just put the "top 1" into the original select statement? You also did not take into consideration "it should exclude tables named dtproperties and sysdiagrams".

    EDIT: We will get to the count next. I just want to help walk you through the thought process with the hopes it will help you learn.
    Code:
    USE AP
    
    Declare @MyRowCount int
    
    SELECT Top 1 name 
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc
    
    SET @MyRowCount = @@ROWCOUNT
    ;

  7. #7
    Join Date
    Oct 2013
    Posts
    17

    reply

    Quote Originally Posted by clawlan View Post
    You sort of did that in a round about way. Why not just put the "top 1" into the original select statement? You also did not take into consideration "it should exclude tables named dtproperties and sysdiagrams".

    EDIT: We will get to the count next. I just want to help walk you through the thought process with the hopes it will help you learn.
    Code:
    USE AP
    
    Declare @MyRowCount int
    
    SELECT Top 1 name 
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc
    
    SET @MyRowCount = @@ROWCOUNT
    ;

  8. #8
    Join Date
    Oct 2013
    Posts
    17

    reply to clawlan

    it wont let me reply clawlan post so I have to do it this way

    Code:
    USE AP
    
    Declare @MyRowCount int
    
    SELECT Top 1 name 
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc
    
    SET @MyRowCount = @@ROWCOUNT
    ;

  9. #9
    Join Date
    Oct 2013
    Posts
    17

    help

    It wont let me reply to post anymore

  10. #10
    Join Date
    Oct 2013
    Posts
    17

    ..

    Code:
    USE AP
    
    Declare @MyRowCount int
    
    SELECT Top 1 name 
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc
    
    SET @MyRowCount = @@ROWCOUNT
    ;
    this is my fith time trying to post this response..

  11. #11
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by dariusd7 View Post
    It wont let me reply to post anymore
    You replied to a post saying you can't reply, haha

  12. #12
    Join Date
    Oct 2013
    Posts
    17

    ..

    Quote Originally Posted by clawlan View Post
    You replied to a post saying you can't reply, haha
    It wont let me add my code. This is now the 7th attempt to add my code.

  13. #13
    Join Date
    Oct 2013
    Posts
    17

    ;,;

    Code:
    USE AP
    
    Declare @MyRowCount int
    
    SELECT Top 1 name 
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc
    
    SET @MyRowCount = @@ROWCOUNT
    ;

  14. #14
    Join Date
    Oct 2013
    Posts
    17

    ...f

    i jsut tried again..it wont let me add my code..this is so frustrating... ughh.. It will except me just typing text.. so im going to have to do it with out the code incanptions around it.

    USE AP

    Declare @MyRowCount int

    SELECT Top 1 name
    FROM sys.tables
    WHERE name not like 'dtproperties' and name not like 'sysdiagrams'
    order by name asc

    SET @MyRowCount = @@ROWCOUNT
    ;

  15. #15
    Join Date
    Oct 2013
    Posts
    17

    bbb

    why is this doing this.. this is so frustrating...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •