dynamic query enhancement - 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)

dynamic query enhancement

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-13 : 10:51:55

The following dynamic query returns a list of tables some of which do not have records in them. Can someone help me out, I am trying to exclude the tables with no records returned? Also, I want to exclude tables where there is not a rn_create_user or rn_edit_user defined in the table?

DECLARE @TableName sysname, @Sql varchar(8000)

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

WHILE @TableName IS NOT NULL
BEGIN
SELECT @Sql='select o.*
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 11:03:09

Suggestions made here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102643 ?

DECLARE @TableName SYSNAME,
	@SQL VARCHAR(8000)

SET	@TableName = ''

WHILE 1 = 1
	BEGIN
		SELECT TOP 1	@TableName = QUOTENAME(TABLE_NAME)
		FROM		INFORMATION_SCHEMA.COLUMNS
		WHERE		COLUMN_NAME IN ('rn_create_user', 'rn_edit_user')
				AND TABLE_NAME > @TableName
		GROUP BY	TABLE_NAME
		HAVING		COUNT(DISTINCT COLUMN_NAME) = 2
		ORDER BY	TABLE_NAME

		IF @TableName IS NULL
			BREAK

		SET	@SQL = 'SELECT		o.*
				FROM		' + @TableName + ' AS o
				LEFT JOIN	User AS u ON u.User_ID IN (o.m_create_user, o.m_edit_user)
				WHERE		u.User_ID IS NULL'

		EXEC (@SQL)
	END

E 12°55'05.25"
N 56°04'39.16"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-13 : 11:40:32

seems to be in an endless loop of some type using:

DECLARE @TableName SYSNAME,
@SQL VARCHAR(8000)

SET @TableName = ''

WHILE 1 = 1
BEGIN
SELECT TOP 1 @TableName = QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('rn_create_user', 'rn_edit_user')
AND TABLE_NAME > @TableName
GROUP BY TABLE_NAME
HAVING COUNT(DISTINCT COLUMN_NAME) = 2
ORDER BY TABLE_NAME

IF @TableName IS NULL
BREAK

SET @SQL = 'SELECT o.*
FROM ' + @TableName + ' AS o
LEFT JOIN users AS u ON u.users_id IN (o.rn_create_user, o.rn_edit_user)
WHERE u.users_id IS NULL'

EXEC (@SQL)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 12:47:13

If you put a PRINT @TableName statement in the loop, and then cancel the execution, which names are printed?

E 12°55'05.25"
N 56°04'39.16"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-13 : 13:17:34

Action_Object_Permissions is the table name, first one in our list of tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources