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!
VoodooFrogAsked:
Who is Participating?
 
deightonprogCommented:
try this stuff

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);

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 = [name]
  FROM syscolumns 
 WHERE [id] IN (SELECT [id] 
                  FROM sysobjects 
                 WHERE [name] = @table)
   AND colid IN (SELECT SIK.colid 
                   FROM sysindexkeys SIK 
                   JOIN sysobjects SO ON SIK.[id] = SO.[id]  
                  WHERE SIK.indid = 1
                    AND SO.[name] = @table);

	
	
	
	SET @sql = 'SELECT ''' + @table + ''' AS TheTable, ' + '''' + @Field + ''' AS TheField, ' + @Field + ' AS FieldVAL ' +  ', ' + @TheKey + ' AS TheKey ' + '  FROM ' + @TABLE + ' WHERE ' + @FIELD + ' IS NULL';
		print @sql
	
	execute (@sql)
	

	
FETCH NEXT FROM vendor_cursor 
    INTO @table, @field;

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Open in new window

0
 
deightonprogCommented:
is the primary key always single field, or can it be composite key?
0
 
VoodooFrogAuthor Commented:
For the tables in this specific situation all use a single field.
0
 
deightonprogCommented:
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
0
 
VoodooFrogAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.