October 23, 2014 at 4:25 pm
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:27 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).
I dont know why but i can't seem to post my code here now.. I tried several tiems and it never shows up..it only shows up as a quote..so sorry its not in the proper format..
October 23, 2014 at 4:31 pm
You have posted your code 4 times. It's easy to understand even if it's showed as a quote. When posting, review that you're using the correct tags.
Follow the instructions in my previous post and ask any specific question that you have.
October 23, 2014 at 4:42 pm
dariuslearn (10/23/2014)
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
;
When you say build a string around it, do you mean using an EXEC...{EXEC|EXECUTE} ('SQL_string')
October 23, 2014 at 4:51 pm
No, I mean building the string. Something like this:
SELECT Top 1 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc
October 23, 2014 at 5:31 pm
This is as close as i can get.. 1 i cant figure out how to add "Count of" to the column name so that it would be "count of ContactUpdates' and 2 I can't figure out how to add Countof the rows to it.
USE AP
DECLARE @TableNameVar varchar(128)
IF OBJECT_ID('XName') IS Not NULL
DROP TABLE XName
SET @TableNameVar = 'Create TABLE XName('Select @TableNameVar = @TableNameVar +
' [' + name +'] bit,'
From sys.tables
where name in
(select Top 1 name
from sys.tables where name not like 'dtproperties' and name not like 'sysdiagrams')
order by name
set @TableNameVar = @TableNameVar + ')'
EXEC (@TableNameVar)
Select * from Xname
;
October 23, 2014 at 5:53 pm
You don't need to create a table each time you need to query something. A SELECT will be enough.
Check the following articles:
COUNT(): http://msdn.microsoft.com/en-us/library/ms175997.aspx
QUOTENAME(): http://msdn.microsoft.com/en-us/library/ms176114.aspx
Dynamic SQL: http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
October 23, 2014 at 6:35 pm
Luis Cazares (10/23/2014)
You don't need to create a table each time you need to query something. A SELECT will be enough.Check the following articles:
COUNT(): http://msdn.microsoft.com/en-us/library/ms175997.aspx
QUOTENAME(): http://msdn.microsoft.com/en-us/library/ms176114.aspx
Dynamic SQL: http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
I have no idea how to accomplish this with just a select query.. That is how I orignally tried to do it for over 11 hours.. To be honest I'm not even sure how the solution I posted works..but it was in the powerpoint from my class, so I used it. I still have no idea how to combine a string and a variable, because every time I try..by using "+" I always get an error back.. So this is the only way I can get this close is what I posted. I will read the articles you gave, but I learn by seeing examples..So if they don't have good example I'm afraid it wont do me any good..
October 23, 2014 at 6:58 pm
For one moment, forget about dynamic code. Try writing a static query that will do what you need.
When you're sure that you have the correct query, you can use the query from sys.tables to substitute the name of the table (which should appear twice in your query) with the column "name" from sys.tables.
If you can copy and paste the result in the query window and execute it as it is, you're on the right track.
The next step is to assign it to a variable. That's fairly simple as you just have to add it to your query.
DECLARE @sql nvarchar(4000);
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc;
Once you have it in a variable, you just need to execute the code with EXECUTE() or sp_executesql.
I could give you the complete example, but I want you to learn what's going on.
October 23, 2014 at 8:58 pm
Luis Cazares (10/23/2014)
For one moment, forget about dynamic code. Try writing a static query that will do what you need.When you're sure that you have the correct query, you can use the query from sys.tables to substitute the name of the table (which should appear twice in your query) with the column "name" from sys.tables.
If you can copy and paste the result in the query window and execute it as it is, you're on the right track.
The next step is to assign it to a variable. That's fairly simple as you just have to add it to your query.
DECLARE @sql nvarchar(4000);
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc;
Once you have it in a variable, you just need to execute the code with EXECUTE() or sp_executesql.
I could give you the complete example, but I want you to learn what's going on.
is this what your talking about
USE AP
DECLARE @sql nvarchar(4000);
Set @sql = (select top 1 name from sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')order by name asc)
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHeRE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc
print @sql
;
October 23, 2014 at 8:59 pm
dariuslearn (10/23/2014)
Luis Cazares (10/23/2014)
For one moment, forget about dynamic code. Try writing a static query that will do what you need.When you're sure that you have the correct query, you can use the query from sys.tables to substitute the name of the table (which should appear twice in your query) with the column "name" from sys.tables.
If you can copy and paste the result in the query window and execute it as it is, you're on the right track.
The next step is to assign it to a variable. That's fairly simple as you just have to add it to your query.
DECLARE @sql nvarchar(4000);
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc;
Once you have it in a variable, you just need to execute the code with EXECUTE() or sp_executesql.
I could give you the complete example, but I want you to learn what's going on.
is this what your talking about
USE AP
DECLARE @sql nvarchar(4000);
Set @sql = (select top 1 name from sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')order by name asc)
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHeRE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc
print @sql
;
I still cannor figure out how to do the count
October 24, 2014 at 8:06 am
If you use sys.tables and join to sys.columns on object_id, you can use COUNT on the column names or ids to get the total. Max_Column_Id_Used in sys.tables would give you the correct number most of the time, but it won't always work.
October 24, 2014 at 8:19 am
Ok i tried to figure this out but can't. I handed in the homework with out answering this problem because I could not figure it. Every method i tried led to a dead in. So what I am asking is if someone could have a heart and just show me a completed example. I read as much as i can and tried all kinds of code. I don't understand object in SQL right now, and dynamic queries. I need to see an example because i am so confused. SO please can some just write a complete code example. here is every thing i tried. My homework was due on Wednesday i handed it last night. Now i can't focus at work because i can't figure out how to do this.
please don't point to some place to read again, I 'm only going to learn it now by seeing an example.
select @MyRowCount AS NumberOfRows from sys.tables
where name =
SET @MyRowCount = @@ROWCOUNT
ContactUpdates
select @MyRowCount AS NumberOfRows from sys.tables
where name = 'ContactUpdates'
SET @MyRowCount = @@ROWCOUNT
select sum(@MyRowCount) as Count from ContactUpdates
USE AP
DECLARE @TableName varchar(128)
DECLARE @MyTableCount int
SET @TableName = (select Top 1 name from sys.tables)
Select
print 'Count of ' + convert(Varchar,@TableName )
DECLARE @TableNameVar varchar(128)
SET @TableNameVar = 'sys.tables'
EXEC ('SELECT Count(name) as count_of FROM ' + @TableNameVar)
USE AP
go
Create view TableCount AS
Select * from sys.tables
where name in (select Top 1 name from sys.tables)
go
select * from TableCount
;
DECLARE @TableNameVar varchar(128)
IF OBJECT_ID('XName') IS Not NULL
DROP TABLE XName
SET @TableNameVar = 'Create TABLE XName('Select @TableNameVar = @TableNameVar +
' [' + name +'] bit,'
From sys.tables
where name in
(select Top 1 name
from sys.tables where name not like 'dtproperties' and name not like 'sysdiagrams')
order by name
set @TableNameVar = @TableNameVar + ')'
EXEC (@TableNameVar)
Select * from Xname
;
USE AP
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT sys.tables.name AS TableName, sys.columns.name
AS ColumnName, sys.types.name AS Type
INTO #TableSummary
FROM sys.tables
JOIN sys.columns ON sys.tables.object_id =
sys.columns.object_id
JOIN sys.types ON sys.columns.system_type_id =
sys.types.system_type_id
WHERE sys.tables.name IN
(SELECT top 1 name
FROM sys.tables
WHERE name NOT IN ('dtproperties', 'TableSummary',
'AllUserTables'))
IF OBJECT_ID('tempdb..#AllUserTables') IS NOT NULL
DROP TABLE #AllUserTables
CREATE TABLE #AllUserTables
(TableID int IDENTITY, TableName varchar(128))
GO
INSERT #AllUserTables (TableName)
SELECT Top 1 name
FROM sys.tables
WHERE name NOT IN ('dtproperties', 'TableSummary',
'AllUserTables')
DECLARE @LoopMax int, @LoopVar int
DECLARE @TableNameVar varchar(128), @ExecVar varchar(1000)
SELECT @LoopMax = MAX(TableID) FROM #AllUserTables
SET @LoopVar = 1
WHILE @LoopVar <= @LoopMax
BEGIN
SELECT @TableNameVar = TableName
FROM #AllUserTables
WHERE TableID = @LoopVar
SET @ExecVar = 'DECLARE @CountVar int '
SET @ExecVar = @ExecVar + 'SELECT @CountVar =
COUNT(*) '
SET @ExecVar = @ExecVar + 'FROM ' + @TableNameVar
+ ' '
SET @ExecVar = @ExecVar + 'INSERT #TableSummary '
SET @ExecVar = @ExecVar + 'VALUES (''' +
@TableNameVar + ''','
SET @ExecVar = @ExecVar + '''*Row Count*'','
SET @ExecVar = @ExecVar + ' @CountVar)'
EXEC (@ExecVar)
SET @LoopVar = @LoopVar + 1
END
SELECT * FROM #TableSummary
ORDER BY TableName, ColumnName
USE AP
DECLARE @sql nvarchar(4000);
Set @sql = (select top 1 name from sys.tables
WHERE name NOT IN( 'dtproperties', 'sysdiagrams')order by name asc)
SELECT Top 1
@sql = 'The table I need is ' + name + '. Now I can create a SELECT statement with it'
FROM sys.tables
WHeRE name NOT IN( 'dtproperties', 'sysdiagrams')
order by name asc
exec(@sql)
;
October 24, 2014 at 8:34 am
Assuming I understood your original question, here is a simplified example, per your request. I used information schema to get the answer. Try to rewrite it using sys.tables & sys.columns like your original homework hinted at, and add in the missing clauses to always pull the first table name. The trick with dynamic sql is writing the select statement correctly in the first place because all the extra single quotes will screw you up...constantly.
DECLARE
@sql VARCHAR(MAX),
@TableName VARCHAR(100)
--Get your table name and store it as a variable
SELECT TOP 1 @TableName = Table_Name FROM INFORMATION_SCHEMA.columns
--Create the dynamic statement to count the columns
SET @sql = '
SELECT
[CountOf'+@TableName+'] = COUNT(*)
FROM information_schema.columns
WHERE table_name = '''+@TableName+'''
GROUP BY table_name'
--Print & Execute the statement
PRINT @sql
EXEC(@SQL)
October 26, 2014 at 10:24 pm
You need to learn that if you want to get the count of the rows the main option is to use "SELECT COUNT(*) FROM Table". You need to learn the basics before going to dynamic code.
Here's an option of how I would do it.
DECLARE @sql nvarchar( 400);
--I'm using QUOTENAME to prevent errors for uncommon names in tables such as names with spaces or ] characters
SELECT TOP 1
@sql = 'SELECT COUNT(*) AS ' + QUOTENAME( 'CountOf' + name) + CHAR(13)
+ ' FROM ' + QUOTENAME( name) + ';'
FROM sys.tables
WHERE name NOT IN ('dtproperties', 'sysdiagrams')
ORDER BY name;
PRINT @sql; --Used to debug the code
EXEC sp_executesql @sql;
You need to understand the difference between COUNT(*) and COUNT(ColumnName).
Try to understand what I posted and ask any question that you have.
Learn how to find information and read BOL as reference. It will help you a lot in your day to day learning.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply