Looping through Sys Objects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping through Sys Objects

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 13:12:43

I want to apply the same query for 250 tables (see below).
It would be very painful to code 250 individual queries.
Is there a way to loop through all 250 tables using "sysobjects WHERE type = 'U'", and applying the below code for each table (users table would remain constant)?

select co.*
from company table_type
where not exists(select * from users u
where (u.users_id = type.rn_create_user) or (u.users_id = type.rn_edit_user)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 13:19:26

You can use the undocumented stored procedure sp_Msforeachtable for this provided all tables have same column names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 13:23:55

More details & example usage here:-

http://www.databasejournal.com/features/mssql/article.php/3441031

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 13:30:45

I am not sure this is the best approach, I get the following error when I run the sp_MSforeachtable stored procedure: "The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid."

Below is my query:

EXECUTE sp_MSforeachtable 'select o.*
from ? o
where not exists(select * from users u
where (u.users_id = o.rn_create_user) or (u.users_id = o.rn_edit_user)
)';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 13:37:03

quote:
Originally posted by qman

I am not sure this is the best approach, I get the following error when I run the sp_MSforeachtable stored procedure: "The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid."

Below is my query:

EXECUTE sp_MSforeachtable 'select o.*
from ? o
where not exists(select * from users u
where (u.users_id = o.rn_create_user) or (u.users_id = o.rn_edit_user)
)';

Another way is to use this method which involves dynamic sql

DECLARE @TableName sysname

SELECT @TableName=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES

WHILE @TableName IS NOT NULL
BEGIN
SELECT @Sql='select co.*
from ' + @TableName +
'o where not exists(select * from users u
where (u.users_id = o.rn_create_user) or (u.users_id = o.rn_edit_user)
)'

EXEC (@Sql)

SELECT @TableName=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME>@TableName

END

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 13:50:20

Hi Visakh16, minor point.....
When run, I get" "Must declare the scalar variable "@Sql"."
Where should this be declared and of what type?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 13:55:22

quote:
Originally posted by qman

Hi Visakh16, minor point.....
When run, I get" "Must declare the scalar variable "@Sql"."
Where should this be declared and of what type?

with this

DECLARE @TableName sysname,@Sql varchar(8000)

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 14:12:29

It appears that a single character of 'o' is being appended to the table name causing the table not to be found......

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 14:15:07

quote:
Originally posted by qman

It appears that a single character of 'o' is being appended to the table name causing the table not to be found......

Thanks

DECLARE @TableName sysname,@Sql varchar(8000)

SELECT @TableName=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES

WHILE @TableName IS NOT NULL
BEGIN
SELECT @Sql='select co.*
from ' + @TableName +
'   o where not exists(select * from users u
where (u.users_id = o.rn_create_user) or (u.users_id = o.rn_edit_user)
)'

EXEC (@Sql)

SELECT @TableName=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME>@TableName

END

There's a space at start of string after @tableName

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 14:19:45

Getting closer......

Now showing:

Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 1
The column prefix 'co' does not match with a table name or alias name used in the query.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 14:26:59

I am a little slow, I fixed that.....

I still get a message of "The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid." when running the dynamic query.

Is there anything that can be done in this case?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 14:38:34

quote:
Originally posted by qman

I am a little slow, I fixed that.....

I still get a message of "The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid." when running the dynamic query.

Is there anything that can be done in this case?

You will get depending on records retrieved on each iterations. Remember you are trying to retrieve records from each table in db that matches the condition with your users table. This might give you a huge amount of records. If you want you can restrict the result set by using a TOP 10 or TOp 5 in your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources