July 30, 2012 at 2:22 pm
Hey I am using sql server 2008 and I need to complete this for a class I am taking and have no exp with dynamic sql
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. Exclude system tables and views. Name the column CountOfTable
It gives a hint: Use the Sys.tables Catalog view
July 30, 2012 at 2:27 pm
Show us what you've tried. We don't do people's homework for them, we'll offer advice and suggestions.
Start with the hint given, how do you determine the first table alphabetically in a database?
Also check your textbook (or speak to the class lecturer) about the basics of dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 3:07 pm
Not really sure how to get the first table alphabetically in the database by writing it in the script. I mean I know which one I need but do not have a clue as to how to make it my result set.
July 30, 2012 at 3:09 pm
Show us the query you would use to return the list of tables from sys.tables in alphabetical order.
July 30, 2012 at 3:14 pm
Like this?
Select Sys.tables.name
From sys.tables
Order By name Asc
July 30, 2012 at 3:18 pm
joshphillips7145 (7/30/2012)
Like this?Select Sys.tables.name
From sys.tables
Order By name Asc
Yes, this works.
First recommendation, stay away from 3 (and 4) part naming in your select list. This is actually being depreciated and may be removed from future versions of SQL Server.
Here is how I would write this query:
select
tab.name
from
sys.tables tab
order by
tab.name Asc; -- You may want to get used to using the ; to terminate statements
Now, modify it to return the first table only.
July 30, 2012 at 3:25 pm
Select Top 1 tab.name
From sys.tables tab
Order By name Asc;
how about this?
July 30, 2012 at 3:43 pm
joshphillips7145 (7/30/2012)
Select Top 1 tab.nameFrom sys.tables tab
Order By name Asc;
how about this?
Be sure to use the table alias in the order by clause as well:
Select Top 1
tab.name
From
sys.tables tab
Order By
tab.name Asc;
Now, keep this handy for use in the future.
Now, write the query that returns the count you need for the table returned by the query above.
July 30, 2012 at 4:16 pm
SELECT
sysobjects.Name
, sysindexes.Rows
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = 'U'
AND sysindexes.IndId < 2
ORDER BY
sysobjects.Name
This returns the count for all tables in the database, I just can't figure out how to just make the one table
I need to be the result set.
July 30, 2012 at 4:24 pm
joshphillips7145 (7/30/2012)
SELECTsysobjects.Name
, sysindexes.Rows
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = 'U'
AND sysindexes.IndId < 2
ORDER BY
sysobjects.Name
This returns the count for all tables in the database, I just can't figure out how to just make the one table
I need to be the result set.
One question, what version of SQL Server are you using? If you are using SQL Server 2005 or later, you really shouldn't be using sysobjects and sysindexes.
Check out the following:
http://msdn.microsoft.com/en-us/library/ms187997(v=sql.100).aspx
Next, running the first query you wrote returns one table. Using that table, write the query you need to return the value you need. I would expect to see the table in question hard-coded in the query at this time.
I am trying to step you forward one small step at a time.
Also, looking at what you posted above, this can be done without using dynamic sql.
EDIT: Read back and saw that you are using SQL Server 2008. Updated the link above to SQL Server 2008 as well.
July 30, 2012 at 4:55 pm
Lynn Pettis (7/30/2012)
Also, looking at what you posted above, this can be done without using dynamic sql.
Yup, but if it's a homework exercise on dynamic SQL, then the answer that's required will use dynamic sQL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 5:46 pm
yes it definitely has to be dynamic sql
July 30, 2012 at 7:54 pm
All I said was it could be done without dynamic sql.
Also, based on what you need it can be done dynamically without using the system tables you used in the second query.
Still waiting for that query I requested.
It is the next step. Think about using the COUNT function.
July 30, 2012 at 8:34 pm
Keep going, Lynn... teaching by revelation like you're doing teaches more than just the ability to write code. You're also teaching someone how to attack a problem. Like they say, "How do you eat an elephant? One bite at a time."
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply