[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Look through Tables

Posted on 2014-07-17
18
Medium Priority
?
224 Views
Last Modified: 2014-07-17
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

0
Comment
Question by:conceptdata
  • 7
  • 6
  • 5
18 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40201593
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
 

Author Comment

by:conceptdata
ID: 40201601
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40201608
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40201612
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
 

Author Comment

by:conceptdata
ID: 40201622
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40201637
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
 

Author Comment

by:conceptdata
ID: 40201639
exactly
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40201642
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40201644
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40201652
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
 

Author Comment

by:conceptdata
ID: 40201664
> 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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40201673
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
 

Author Comment

by:conceptdata
ID: 40201679
Together with your earlier code :):)
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 300 total points
ID: 40201685
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
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 40201700
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
 

Author Comment

by:conceptdata
ID: 40201703
Works fine.
What about the Edit mode and not showing the table headlines if no rows are affected ??
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40201733
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
 

Author Comment

by:conceptdata
ID: 40201738
Ok Thanks.

I understand.

Closing :):)

And thanks for the fast answers
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question