Find table(s) name and column(s) name by providing a value in SQLServer-2000 Database

Many times(specially bug fixing period) developer wants to search a specific value is present in database or the value is in which tables & which columns. If value found in database then easily understand the value is comming from database otherwise think it comes from application code or xml file.

Most of the case data value comes from database. So developer first guess the value is in database. But it should be proved. otherwise bug cannot be solved.


I worte 2 stored procedure for searching data in database. Any one can call it form application and make a tools or directly execute it from database. the code examples are


1st SP
=====

create proc dbo.GetColumnName
(
@tableName varchar(1000),
@value varchar(1000)
)
as

SET NOCOUNT ON

declare @colName varchar(1000),
@sql nvarchar(4000),
@counter int;

set @sql = '';

set @counter = 1;

create table #Temp (sl int identity(1,1), [Name] varchar(1000));

create table #TempNew ([Value] varchar(1000));

create table #Result(name varchar(1000));

set @sql = N'insert #temp ([Name]) select name from syscolumns
where id = object_id(''' + @tableName + ''')' + ' AND XType NOT IN(36,61,58,34,35,165,99) ORDER BY name ASC';


EXEC SP_EXECUTESQL @sql while(1=1) begin

select @colName = [Name] from #temp where sl = @counter;

if (@@rowcount = 0) break;

--print @colName;

set @sql = N'insert #TempNew([Value])
select ' + @colName + ' from ' + @tableName
+ ' where ' + 'CAST('+ @colName + ' AS VARCHAR(1000))=''' + @value + '''';

EXEC sp_executesql @sql;

IF (@@ROWCOUNT > 0) --print @colName;

INSERT INTO #Result(name) VALUES(@colName);

set @counter = @counter + 1;

end
--print @counter; select all * from #Result;
go
--exec dbo.GetColumnName 'dbo.reader' , 3


2nd SP
=====

CREATE PROCEDURE dbo.GetColumnNameFromDB
(
@Value VARCHAR(8000)
)
AS

SET NOCOUNT ON
DECLARE @NewTable VARCHAR(1000)

IF (OBJECT_ID('tempdb.dbo.##Result') IS NOT NULL)
DROP TABLE tempdb.dbo.##Result;

CREATE TABLE tempdb.dbo.##Result(SL INT IDENTITY(1,1), TableName Varchar(1000), ColumnName varchar(1000))

--DECLARE @Result TABLE(SL INT IDENTITY(1,1), TableName Varchar(1000), ColumnName varchar(1000)) DECLARE C1 CURSOR FOR SELECT table_name FROM information_schema.tables
WHERE table_type='BASE TABLE'

OPEN C1

FETCH NEXT FROM C1 INTO @NewTable;

WHILE(@@FETCH_STATUS = 0) BEGIN

INSERT ##Result(ColumnName)
EXEC dbo.GetColumnName @TableName = @newTable, @Value = @Value;

IF (@@ROWCOUNT > 0)

UPDATE ##Result SET TableName = @NewTable WHERE SL = (SELECT MAX(SL) FROM ##Result)

FETCH NEXT FROM C1 INTO @NewTable; END CLOSE C1 DEALLOCATE C1;

SELECT ALL * FROM ##Result;

GO

Oracle Object Name - Casing Issue

One interesting things find in oracle (if you are a Sql server developer) is that if I create objects name like MyTable then oracle transform it MYTABLE (upper case).

If I want to maintain case then I should use "MyTable"(with double quate).

Sometimes It make me confuse. Speciall when create view. Suppose I Create a view like
CREATE VIEW "MyView"ASSELECT id AS "ID", name AS "Name" FROM "MyTable"
If I call that view from my application then I should use

string SQL = "SELECT V.ID from MyView V WHERE V.Name = 'habib';

If any application wants to support 2 databases like SQLServer & ORACLE then

it is very difficult to maintain casing. Because SQL Server developer never thinkcasing issue about database table/view/columns naming. If you convert Sql ServerObject to Oracle then always you should think and maintain that issue. OtherwiseOracle always gives you upper case object name. Some times It create problem of databinding.

For Example

MyComboBox.DataSource = MyDataSet[MyTable];
MyComboBox.DisplayMemeber = "Id";

MyComboBox.ValueMemeber = "Name";

If Database is Sql SERVER then whatever the case is, its does not create any problem.

But If it is Oracle then Oracle sent you [ID, NAME].

That ComboBox DataBinding behaves
interesting.

Why I am saying intersting, if you want to know then please write a demo
project and create the situation.

Dot Net implecit Casting Problem for Oracle Database

When you try to implecit convert integer from a ado.net' datarow, if database is oracle then it though Invalid casting error. You must use explicit casting with the help of Convert class( Convert.ToInt32()) to convert that.
But it is interesting that if your database is SQL Server then it works fine.