[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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!
0
VoodooFrog
Asked:
VoodooFrog
  • 3
  • 2
1 Solution
 
deightonCommented:
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
 
deightonCommented:
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
 
deightonCommented:
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now