October 23, 2014 at 2:44 pm
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.
October 23, 2014 at 2:48 pm
You can use system procedure:
sp_rename
to rename a column. The general format is like this:
EXEC sp_rename 'table_name.existing_column_name', 'new_column_name', 'COLUMN'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 2:48 pm
Can you post what you have so far please? It's not actually hard to do the highlighted portion, but easier to add to your existing code than write from scratch.
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
October 23, 2014 at 2:49 pm
ScottPletcher (10/23/2014)
You can use system procedure:sp_rename
to rename a column.
He's trying to alias a column in a query with a dynamic name, not rename a column in a table.
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
October 23, 2014 at 2:59 pm
Sorry [that did seem rather obscure for a starting class on SQL].
In the SELECT list, after the original column name or expression, you can add "AS new_column_name", and the query will list the column under the new name in the results.
For example:
SELECT object_id, name AS table_name
FROM sys.tables
WHERE name = 'table_name'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 3:23 pm
ScottPletcher (10/23/2014)
Sorry [that did seem rather obscure for a starting class on SQL].In the SELECT list, after the original column name or expression, you can add "AS new_column_name", and the query will list the column under the new name in the results.
For example:
SELECT object_id, name AS table_name
FROM sys.tables
WHERE name = 'table_name'
Sorry this doesn't work. Se the column name should be equal to the name of the talbe form the result.I already know that the table name is ContactUpdates cause I did a select query to see what was the first table listed in sys.tables. So when the query is run it should name the name column to ContactUpdates.
so the table name resulting from the following query should become the column name.
SELECT name
FROM sys.tables
WHERE name = (select Top 1 name from sys.tables)
;
The resulting table name from this query is ContactUpdates.. now i need to have the script rename the column name to 'count of ContactUpdates' as instructed in the assignment..
October 23, 2014 at 3:37 pm
dariuslearn (10/23/2014)
ScottPletcher (10/23/2014)
Sorry [that did seem rather obscure for a starting class on SQL].In the SELECT list, after the original column name or expression, you can add "AS new_column_name", and the query will list the column under the new name in the results.
For example:
SELECT object_id, name AS table_name
FROM sys.tables
WHERE name = 'table_name'
Sorry this doesn't work. Se the column name should be equal to the name of the talbe form the result.I already know that the table name is ContactUpdates cause I did a select query to see what was the first table listed in sys.tables. So when the query is run it should name the name column to ContactUpdates.
so the table name resulting from the following query should become the column name.
SELECT name
FROM sys.tables
WHERE name = (select Top 1 name from sys.tables)
;
The resulting table name from this query is ContactUpdates.. now i need to have the script rename the column name to 'count of ContactUpdates' as instructed in the assignment..
Indeed you do need to rename your column that way; my code was not to write it for you, just to give you a working example. I will note that you'll need quotes around the name if it has spaces in it:
SELECT object_id, name AS "table name"
And you need to add "ORDER BY name" to your subquery above.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 3:48 pm
dariuslearn (10/23/2014)
so the table name resulting from the following query should become the column name.
SELECT name
FROM sys.tables
WHERE name = (select Top 1 name from sys.tables)
;
You don't need to read the table twice. This will give almost the same result (mine will ensure that the order is correct)
SELECT TOP 1 name
FROM sys.tables
ORDER BY name;
As Gail said, please share what you have done to show you what you're missing (which is fairly simple).
October 23, 2014 at 3:49 pm
Just a thought, dynamic SQL, selecting the table names into a variable and loop through the resulting set of table names excecuting the count query for each.
conceptually ...
select the table names from the catelogue view into a variable, looping until there are no more table names.
For each returned table name execute a dynamic SQL statement to get the required result set.
Create a dynamic SQL statement using the varable name, along the lines of
set @ColumnAlias = 'Countof' + @TableName
set @SQL = 'Select count(*) as ' + @ColumnAlias + ' from ' + @tablename
You could insert the results of the dynamic SQL into a table and then select the data from the table.
I know it is not an elegant solution and I would most likely never do this in production but from answering the assignement it will get you there.
October 23, 2014 at 3:59 pm
Luis Cazares (10/23/2014)
dariuslearn (10/23/2014)
so the table name resulting from the following query should become the column name.
SELECT name
FROM sys.tables
WHERE name = (select Top 1 name from sys.tables)
;
You don't need to read the table twice. This will give almost the same result (mine will ensure that the order is correct)
SELECT TOP 1 name
FROM sys.tables
ORDER BY name;
As Gail said, please share what you have done to show you what you're missing (which is fairly simple).
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
;
I also have to figure out how to get a count of the rows in that table and display it under the new column name
October 23, 2014 at 4:03 pm
dogramone (10/23/2014)
Just a thought, dynamic SQL, selecting the table names into a variable and loop through the resulting set of table names excecuting the count query for each.conceptually ...
select the table names from the catelogue view into a variable, looping until there are no more table names.
For each returned table name execute a dynamic SQL statement to get the required result set.
Create a dynamic SQL statement using the varable name, along the lines of
set @ColumnAlias = 'Countof' + @TableName
set @SQL = 'Select count(*) as ' + @ColumnAlias + ' from ' + @tablename
You could insert the results of the dynamic SQL into a table and then select the data from the table.
I know it is not an elegant solution and I would most likely never do this in production but from answering the assignement it will get you there.
Why do you want to loop? SQL Server can read and generate the statements in a single scan. In addition to that, the OP just needs one table.
October 23, 2014 at 4:04 pm
dogramone (10/23/2014)
Just a thought, dynamic SQL, selecting the table names into a variable and loop through the resulting set of table names excecuting the count query for each.conceptually ...
select the table names from the catelogue view into a variable, looping until there are no more table names.
For each returned table name execute a dynamic SQL statement to get the required result set.
Create a dynamic SQL statement using the varable name, along the lines of
set @ColumnAlias = 'Countof' + @TableName
set @SQL = 'Select count(*) as ' + @ColumnAlias + ' from ' + @tablename
You could insert the results of the dynamic SQL into a table and then select the data from the table.
I know it is not an elegant solution and I would most likely never do this in production but from answering the assignement it will get you there.
Why would I need to loop through all tables? when I can just use top 1 to get the very first table..
October 23, 2014 at 4:12 pm
Your variable @MyRowCount will always return 1 or 0 because you're limiting the results.
You have a query that will return the name of the table. Using that value, build a string with the statement to count all rows in the table. Follow previous advises to understand how to alias a column. Use QUOTENAME() function to prevent problems with names. Once you're sure that you have the correct string, assign it to a variable. Using the variable, you can search about how to execute dynamic sql.
EDIT: If you need additional help, feel free to continue asking. You can also find information on BooksOnLine(BOL) which is SQL Server help and you can access to it by pressing F1 on SSMS.
October 23, 2014 at 4:14 pm
sorry I am having trouble responding to post. This is the code I have so far.
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
;
October 23, 2014 at 4:24 pm
This is what i have so far.
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
;
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply