SELECT FROM @variable
-
You can't do that... and it's really not a good idea to even try to!
What do you need to be able to do exactly?
-
I have a query that retrieves the table names where certain column names appear. Something like this
SELECT DISTINCT so.name
FROM syscolumns sc
JOIN sysobjects so
ON sc.id = so.id
AND sc.name IN ('AccessUserName'...
So I then get a list of table names. I then want to step thru each table to search for a certain user. How can I do this?
-
You can use a cursor and step through each table searching for the users and store the found information in a temp table.
-
I've tried that but the cursor is a variable and it doesn't work and thus this posting.
DECLARE @ObjectName varchar(100) -- tried with and without this using ObjectCursor directly in the alternative
DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT so.name
FROM syscolumns sc
JOIN sysobjects so
ON sc.id = so.id
AND sc.name IN ('AccessUserName'...)
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 * FROM @ObjectName
FETCH NEXT FROM ObjectCursor INTO @ObjectName
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
-
DECLARE @SQL AS nVARCHAR(4000)
SET @SQL = 'SELECT WHATEVER FROM ' + @ObjectName
EXEC (@SQL)
-
Got it!
Thanks!!!
-
Use only as a last resort. This is a dangerous tool when used inappropriately.
Search for sql injection for more details.
-
Sorry about the formatting but when I cut/paste the editor mucks up the formatting something aweful. In any case the following should work for you and is pretty resistant to SQL injection attacks.
DECLARE @TableCount INTEGER,
@DynamicSQL NVARCHAR(4000),
@TableName SYSNAME
@UserToFind SYSNAME
SET @UserToFind = 'EdHammar'
DECLARE @Tables TABLE
([ID] INTEGER NOT NULL IDENTITY(1, 1),
[TableName] SYSNAME)
INSERT @Tables
SELECT SO.[name]
FROM SYSOBJECTS AS SO
JOIN SYSCOLUMNS AS SC
ON SO.[id] = SC.[id]
WHERE OBJECTPROPERTY(SO.[ID], 'IsUserTable') = 1
AND SC.[name] = 'AccessUserName'
SELECT @TableCount = SCOPE_IDENTITY(),
@TableName = ''
WHILE @TableCount > 0
BEGIN
SELECT @TableName = [TableName],
@Exists = NULL
FROM @Tables
WHERE [ID] = @TableCount
SET @DynamicSQL = 'SELECT @Exists = 1 FROM ' + @TableName + ' WHERE AccessUserName = @UserToFind'
EXECUTE sp_executesql
@DynamicSQL,
N'@UserToFind SYSNAME, @Exists BIT OUTPUT',
@UserToFind,
@Exists OUTPUT
IF @Exists IS NOT NULL
PRINT 'The user exists in table ' + @TableName
SET @TableCount = @TableCount - 1
END
-
It's always bullet proof when you query the system tables to find information (unless an object is named : ";--drop table dbo.Users").
Anyhow, I'd strongly suggest you read articles on sql injection. You'll certainly learn something from that.
-
Let him enter the field of landmines by using dynamic SQL.
See http://www.sommarskog.se/dynamic_sql.html
N 56°04'39.16"
E 12°55'05.25" -
Thanks ED. This is good if I am searching one column. But I am searching many columns that may or not exist in each table.
...AND sc.name IN ('AccessUserName', 'CancelledUserName', 'CancelUserName', 'CompletedUserName', 'CreateUserName',
'DeleteUserName', 'DisplayName', 'ExportUserName', 'HeldBy', 'ImagedUserName', 'LastUsedUserName',
'ModifyUserName', 'name', 'NotifiedUserName', 'SysUsers_Name', 'UserFName', 'UserLName', 'UserName')
From the list of tables retrieved, I then need to see if the [column] = . Again, while all of the columns do exist in the database, perhaps only a few may exist in each table.
I think the @Tables should be modified to hold the column name as well. Then search that column in that table for that user. However, even if that is right, I need help on the SET @DynamicSQL clause to just out put each row where that user exists.
-
"It's always bullet proof when you query the system tables to find information (unless an object is named : ';--drop table dbo.Users')."
If someone is creating tables with names like that in my database, I've got far bigger issues than that query of mine and they aren't technical issues, they are human resource and procedural issues.
That's not to say I advocate dynamic SQL and I'm certainly not trying to minimize the exposure you open yourself up to with dynamic SQL. However, there is a place for dynamic SQL and it is an invaluable tool which can be used to save time/money and make a business more competitive. The advice against dynamic SQL is appropriate and you should always be extremely cautious when implementing any code which utilizes dynamic SQL.
David, to answer you are on the right track. Simple capture the column names for each table you are interested in. Then in the loop test for the existance of the column in the table, if the column exists then run your query against that column.
-
I just got it. Thanks for your help!
-
Another good reason to use square brackets:
DECLARE @SQL AS nVARCHAR(4000)
SET @SQL = 'SELECT WHATEVER FROM ' + QUOTENAME(@ObjectName)
EXEC (@SQL)
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply