Get Column name & table name from value

  • Hi

    Actually i have a new client ,& their Database has no standard naming conventions & the application is in classic asp.I have a form in which a form there are many values in the different textboxes ,it it very difficult to trace the value come from which table.& also there is no erd.

    I need a query from which i can get the table name with column name by giving Value

    Thanks

  • ALI100 (7/10/2012)


    Hi

    Actually i have a new client ,& their Database has no standard naming conventions & the application is in classic asp.I have a form in which a form there are many values in the different textboxes ,it it very difficult to trace the value come from which table.& also there is no erd.

    I need a query from which i can get the table name with column name by giving Value

    Thanks

    I did not understand your requirement what exactly you are looking for.

    Either you need the tables used in that form or all the tables in database.

    For first you can have SQL profiler while executing the form and you will get the associated tables and column.For second you can execute query on sys.tables and sys.columns

    However to find the value in a perticular textbox you will have to debug your code.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • let's suppose i have a value having lable name abc#= '6599912268'

    & the new project has no ERD no standard of naming conventions... I need a fast way to know the abc# ='6599912268' is taking from which table & which column name.... like this the UI has many values which is time taken to trace manually

    IS there any way to trace it

  • ALI100 (7/10/2012)


    let's suppose i have a value having lable name abc#= '6599912268'

    & the new project has no ERD no standard of naming conventions... I need a fast way to know the abc# ='6599912268' is taking from which table & which column name.... like this the UI has many values which is time taken to trace manually

    IS there any way to trace it

    As I told earlier, I don't think it is not possible to find the table name or column name associated with any form control except you will have to debug your code.

    However you can post your query on some other forums like StakeOverflow in Classic ASP tab.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • A quick search on this site gave me the below link

    I hope this will be helpfull

    http://www.sqlservercentral.com/scripts/Miscellaneous/65769/


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • i took over a screwed up database like that once; the table names and column names had been obfuscated, i guess in order to protect someones proprietary schema; they took out all the foreign keys too;

    performance sucked, and it took me quite a while tracing the data to get a proper understanding of the schema;

    I built a suite of views that mapped the tables/columns into human understandable relationships.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Kingston Dhasian (7/11/2012)


    A quick search on this site gave me the below link

    I hope this will be helpfull

    http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

    Hello Kingston,

    If I am not wrong then he wants all columns of tables those are used in the form controls and that too specific of perticular control.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • here's what i use , a procedure i built called sp_UglySearch.

    since you are searching every table/column for text, this is slow, and any tables with a Million rows are going to slow things down even more.

    don't run this on production, but on a dev copy on a different machine.

    usage is simple:

    --a table and any columns that contain the searched value

    EXEC sp_UGLYSEARCH '6599912268'

    CREATE PROCEDURE sp_UGLYSEARCH

    /*

    --Purpose: to search every string column in a databasefor a specific word

    --returns sql statement as a string which idnetifies the matching table

    -- or when the optional parameter is used, the sql statement for the specific matching column.

    --usage:

    -- EXEC sp_UGLYSEARCH 'Provisional'

    -- EXEC sp_UGLYSEARCH '6599912268'

    -- creates one SQL for each table that actually has a match for the searched value i.e.

    -- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%' OR [TITLE] LIKE '%6599912268%'

    --optional parameter SEARCHBYCOLUMN

    -- EXEC sp_UGLYSEARCH '6599912268',1

    -- creates one SQL for each Column that actually has a match for the searched value i.e.

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%'

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%6599912268%'

    */

    @SEARCHSTRING VARCHAR(50),

    @SEARCHBYCOLUMN INT = 0

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @sql VARCHAR(max),

    @SCHEMANAME VARCHAR(100),

    @TABLENAME VARCHAR(100),

    @COLUMNNAME VARCHAR(100),

    @COLZ VARCHAR(max)

    CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))

    SELECT

    SCHEMA_NAME(schema_id) AS SCHEMANAME,

    objz.name AS TBLNAME,

    colz.name AS COLNAME,

    TYPE_NAME(colz.user_type_id) AS DATATYPE

    INTO #TEMP

    FROM sys.objects objz

    INNER JOIN sys.columns colz ON objz.object_id = colz.object_id

    WHERE objz.type='U'

    AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

    AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl

    ORDER BY TBLNAME,COLNAME

    IF @SEARCHBYCOLUMN = 0

    BEGIN

    DECLARE C1 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C1

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @COLZ=''

    SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME

    --@COLZ has a trailing 'OR ' which must be removed

    SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)

    --PRINT @COLZ

    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'

    --PRINT @sql

    EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C1

    DEALLOCATE C1

    END

    ELSE --@SEARCHBYCOLUMN <> 0

    BEGIN

    DECLARE C2 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C2

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')

    INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''

    SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'

    PRINT @sql

    EXEC (@SQL)

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C2

    DEALLOCATE C2

    END --@SEARCHBYCOLUMN <> 0

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • rhythmk (7/11/2012)


    Kingston Dhasian (7/11/2012)


    A quick search on this site gave me the below link

    I hope this will be helpfull

    http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

    Hello Kingston,

    If I am not wrong then he wants all columns of tables those are used in the form controls and that too specific of perticular control.

    He probably wants the names of database columns and their respective tables having a specific value

    But, I am not sure and only the OP can confirm if my understanding is correct and that is what he wants


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Lowell (7/11/2012)


    here's what i use , a procedure i built called sp_UglySearch.

    since you are searching every table/column for text, this is slow, and any tables with a Million rows are going to slow things down even more.

    don't run this on production, but on a dev copy on a different machine.

    usage is simple:

    --a table and any columns that contain the searched value

    EXEC sp_UGLYSEARCH '6599912268'

    CREATE PROCEDURE sp_UGLYSEARCH

    /*

    --Purpose: to search every string column in a databasefor a specific word

    --returns sql statement as a string which idnetifies the matching table

    -- or when the optional parameter is used, the sql statement for the specific matching column.

    --usage:

    -- EXEC sp_UGLYSEARCH 'Provisional'

    -- EXEC sp_UGLYSEARCH '6599912268'

    -- creates one SQL for each table that actually has a match for the searched value i.e.

    -- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%' OR [TITLE] LIKE '%6599912268%'

    --optional parameter SEARCHBYCOLUMN

    -- EXEC sp_UGLYSEARCH '6599912268',1

    -- creates one SQL for each Column that actually has a match for the searched value i.e.

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%'

    -- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%6599912268%'

    */

    @SEARCHSTRING VARCHAR(50),

    @SEARCHBYCOLUMN INT = 0

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @sql VARCHAR(max),

    @SCHEMANAME VARCHAR(100),

    @TABLENAME VARCHAR(100),

    @COLUMNNAME VARCHAR(100),

    @COLZ VARCHAR(max)

    CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))

    SELECT

    SCHEMA_NAME(schema_id) AS SCHEMANAME,

    objz.name AS TBLNAME,

    colz.name AS COLNAME,

    TYPE_NAME(colz.user_type_id) AS DATATYPE

    INTO #TEMP

    FROM sys.objects objz

    INNER JOIN sys.columns colz ON objz.object_id = colz.object_id

    WHERE objz.type='U'

    AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

    AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl

    ORDER BY TBLNAME,COLNAME

    IF @SEARCHBYCOLUMN = 0

    BEGIN

    DECLARE C1 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C1

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @COLZ=''

    SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME

    --@COLZ has a trailing 'OR ' which must be removed

    SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)

    --PRINT @COLZ

    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'

    --PRINT @sql

    EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C1

    DEALLOCATE C1

    END

    ELSE --@SEARCHBYCOLUMN <> 0

    BEGIN

    DECLARE C2 CURSOR FOR

    SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME

    OPEN C2

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')

    INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''

    SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'

    PRINT @sql

    EXEC (@SQL)

    FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME

    END

    CLOSE C2

    DEALLOCATE C2

    END --@SEARCHBYCOLUMN <> 0

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME

    END --PROC

    GO

    Lowell, why do you use sys.columns instead of Information_Schema.Columns?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Just a note on forms. So far as I know, a label box doesn't actually point to a database & table. It is just a label. So trying to connect the two is difficult at best. On the other hand, if you are looking at a dropdown box or some other text box (different from a label) you may have the ability to locate the database & table names by looking at the properties. Or you could search the form's XML code and compare that against your list of table and database names.

    If all your boxes are unbound (which can happen), I wish you the best of luck. Unbound boxes are a PITA. Chances are the details are in client code that you can't see without viewing the code for the form. Then maybe you can search that and find any stored procedures or functions, then reference back to your tables.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • the opposite question for me really, is what advantage would there be to use the information_schema instead of the sys tables?

    typically there's more information available in the SQL specific meta data, although in this case my query does not take advantage of it, and you are right, the other views could be used instead.

    at one point, i was trying to create some TSQL code that could make a CREATE TABLE statement from the metadata;

    information_schema does not contain a lot of the information you need to do that.

    try tracking down things like which column has an identity, the identity start value and seed, table indexes, and quite a few other things are just not represented in information_schema, so writing code that mixes sys.* views and information_schema.* views didn't make sense.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Probably for the same reason I do, the sysstem views have more information available than the INFORMATION_SCHEMA views. If I was writing something that had to be portable between different RDBM systems and I could rely completely on the information from those views then I would use them. As I am working solely with SQL Server, I'll use the system views directly and worry about changes to them as needed.

  • Lowell, Ah. Interesting. I didn't think about it that way.

    FYI: Please don't think I was saying "it's bad to use sys.columns." I was just genuinely curious why people would still stick to a system view that is slated for future deprecation (supposedly).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (7/11/2012)


    Lowell, Ah. Interesting. I didn't think about it that way.

    FYI: Please don't think I was saying "it's bad to use sys.columns." I was just genuinely curious why people would still stick to a system view that is slated for future deprecation (supposedly).

    Brandie, The system view sys.columns isn't being depreciated. What is being depreciated is syscolumns which is the system table for backward compatibility to SQL Server 2000.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply