SQL Look through Tables

Hi I have this code that fails on the line "SELECT rownumber FROM @tableName" - Why ?


DECLARE @tableName varchar(50)

DECLARE
  all_tables cursor for
    SELECT table_name
            FROM INFORMATION_SCHEMA.TABLES 
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
	print @tableName
	SELECT rownumber FROM @tableName
	--where rownumber = 0
	
	fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables;

Open in new window

conceptdataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

I think you declared Variable and using it as Table Variable,

Try this

DECLARE @tableName table (rownumber varchar(50))

DECLARE
  all_tables cursor for
    SELECT table_name
            FROM INFORMATION_SCHEMA.TABLES
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      print @tableName
      SELECT rownumber FROM @tableName
      --where rownumber = 0
      
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables;
0
conceptdataAuthor Commented:
Gives me following errors, on the Fetch lines :

Msg 137, Level 16, State 1, Line 9
Must declare the scalar variable "@tableName".
Msg 137, Level 16, State 1, Line 16
Must declare the scalar variable "@tableName".
0
Vikas GargBusiness Intelligence DeveloperCommented:
From where are you getting the Detail for @tableName ?

DECLARE @table table (rownumber varchar(50))
DECLARE @tableName varchar(50)

DECLARE
  all_tables cursor for
    SELECT table_name
            FROM INFORMATION_SCHEMA.TABLES
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      print @tableName
      SELECT rownumber FROM @table
      --where rownumber = 0
      
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables;
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Lee SavidgeCommented:
What are you hoping to do? You're selecting from a varchar which is the problem because as said in the first reply, you're using it as a table variable, but even the first reply doesn't make sense if you create it as a table variable and call the single column, rownumber.
0
conceptdataAuthor Commented:
I will loop throuch all the tables listed in INFORMATION_SCHEMA.TABLES.
Then I will check for 0 in column RowNumber in each rows for each table listed.

Does that make sense :)
0
Lee SavidgeCommented:
Erm, sort of. To clarify, you would like to get a list of all the tables in the schema, and then you would like to select from EACH TABLE, the column called rownumber to see if there are rows with the value of 0?
0
conceptdataAuthor Commented:
exactly
0
Lee SavidgeCommented:
Dynamic SQL then

DECLARE @tableName varchar(50)

DECLARE
  all_tables cursor for
    SELECT table_name
            FROM INFORMATION_SCHEMA.TABLES where  TABLE_TYPE = 'base table'
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      exec ('select rownumber from ' + @tableName + ' where rownumber = 0')
      --SELECT rownumber FROM @tableName
      --where rownumber = 0
      
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables; 

Open in new window

0
Lee SavidgeCommented:
PS. You probably need the where clause in your cursor statement to limit what information_schema.tables returns because it will return views as well. Filter by table_type = 'base table'
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Try this,
and to avoid any error filter tables from INFORMATION_SCHEMA.TABLES where only those column should be there which has Rownumber column


DECLARE @tableName varchar(50),@SQL NVARCHAR(500)

DECLARE
  all_tables cursor for
    SELECT table_name
            FROM INFORMATION_SCHEMA.TABLES
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      
      print @tableName
      set @SQL = 'SELECT rownumber FROM ' + @tableName + ' WHERE ROWNUMBER = 0'
      EXECUTE (@SQL)
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables;
0
conceptdataAuthor Commented:
> Lee Savidge

Works well.

But is it possible to only show the rows with rownumber = 0 - and in modify mode.
And :) not to have the table with that not have the rownumber column :)
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

SELECT *
            FROM INFORMATION_SCHEMA.TABLES
                  WHERE TABLE_NAME IN
                  (SELECT NAME FROM SYS.tables WHERE object_id IN (
                  SELECT object_id FROM SYS.columns WHERE NAME = 'ROWNUMBER'
                  ))
            ORDER BY TABLE_NAME

You will get only those tables which has Rownumber column
0
conceptdataAuthor Commented:
Together with your earlier code :):)
0
Vikas GargBusiness Intelligence DeveloperCommented:
Yes

Complete Query

DECLARE @tableName varchar(50),@SQL NVARCHAR(500)

DECLARE
  all_tables cursor for
    SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
                  WHERE TABLE_NAME IN
                  (SELECT NAME FROM SYS.tables WHERE object_id IN (
                  SELECT object_id FROM SYS.columns WHERE NAME = 'ROWNUMBER'
                  ))
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      
      print @tableName
      set @SQL = 'SELECT rownumber FROM ' + @tableName + ' WHERE ROWNUMBER = 0'
      EXECUTE (@SQL)
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables;
0
Lee SavidgeCommented:
Ok, I think you need to ensure you state your full requirements in the question initially otherwise this ends up as an endless question.

DECLARE @tableName varchar(50)

DECLARE
  all_tables cursor for
select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'rownumber'
and TABLE_NAME in (select TABLE_NAME
from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'base table'
)
            ORDER BY TABLE_NAME ;
open all_tables
fetch next from all_tables into @tableName

while @@FETCH_STATUS = 0
BEGIN
      exec ('select rownumber from ' + @tableName + ' where rownumber = 0')
      --SELECT rownumber FROM @tableName
      --where rownumber = 0
      
      fetch next from all_tables into @tableName
END
close all_tables;
DEALLOCATE all_tables; 

Open in new window


To put it in edit mode, you will need to open an edit window.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
conceptdataAuthor Commented:
Works fine.
What about the Edit mode and not showing the table headlines if no rows are affected ??
0
Lee SavidgeCommented:
Ok, please see my comments above about endless questions. I have no idea what you mean by table headlines. As for edit mode, as I said in the previous comment, you need to open an edit window. When using SQL management studio, the results are displayed as a readonly record set. If you want to edit the results, you need to open a table in the left pane by choosing edit top 200 rows and then change the query. SSMS isn't designed to be an editor.
0
conceptdataAuthor Commented:
Ok Thanks.

I understand.

Closing :):)

And thanks for the fast answers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.