asked on

How do I find all records with a field with a specific value?

Sounds simple, but here is the curve ball: I want to return the table name, field name, and PK for every record that has a bit field that is null. How would I do it?

Thanks!

deighton

is the primary key always single field, or can it be composite key?

VoodooFrog

ASKER

For the tables in this specific situation all use a single field.

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deighton

the values being queried could be inserted into a temporary table and queried at the end as a single query

it is the queries that give the results rather than the print statements

VoodooFrog

ASKER

Alright, a little bit of tweaking and it works. Had to go about getting the PK field name differently. The query you had looked like it should work, but I was getting PK field names that did not match their table.

Thanks for your help!

begin try
drop table #dump3;
end try 
begin catch 
print 'OMG'
end catch 
SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
	INTO #DUMP3
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid;


declare @table varchar(100);
declare @field varchar(100);
declare @sql varchar(500);
declare @TheKey varchar(100);

CREATE TABLE #Temp10172011
(
	ID int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	TableName nvarchar(255),
	FieldName nvarchar(255),
	PK nvarchar(255)
)

DECLARE vendor_cursor CURSOR FOR 
SELECT table_name, column_name
FROM #dump3
WHERE datatype = 'bit'

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor 
INTO @table, @field;


WHILE @@FETCH_STATUS = 0
BEGIN
	
	print @table + ' ' + @field;
	 
    SELECT @TheKey = c.[Name]
    FROM sys.key_constraints as k JOIN
        sys.tables as t ON t.object_id = k.parent_object_id join 
        sys.schemas as s on s.schema_id = t.schema_id join 
        sys.index_columns as ic on ic.object_id = t.object_id AND ic.index_id = k.unique_index_id join 
        sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id 
    WHERE k.type = 'PK' and t.name = @table
	
	SET @sql = 'INSERT INTO #Temp10172011 (TableName, FieldName, PK) SELECT ''' + @table + ''', ''' + @field + ''', [' + @TheKey + '] FROM [' + @table + '] WHERE [' + @field + '] IS NULL'

	print @sql
	
	execute (@sql)
	
    FETCH NEXT FROM vendor_cursor 
    INTO @table, @field;

END

SELECT * FROM #Temp10172011

CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

DROP TABLE #Temp10172011

Open in new window