Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: help search for a column

    Hello,

    I have access to a database's web front end and a limited amount of access to the server.

    Im trying to find out what column a certain string is in.

    I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...

    I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.

    I dont have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"

    any suggestions?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What access do you have to the web server files and the SQL Server directly?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    I can read and run sql commands, Im not too sure what access I have, but I can certainly run queries via SQL commands just fine

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  5. #5
    Join Date
    Dec 2012
    Posts
    63
    this is the error I got:
    Error report:
    Invalid JDBC escape syntax at line position 30 '=' character expected.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    JDBC? How are you running these SQL commands? Can you not use SSMS?
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2012
    Posts
    63
    im running the commands via Oracle SQL Developer... because the company wont let me use SSMS i guess...

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Run this script, after replacing 12345 with the unique Sequence number.
    Code:
    select 'SELECT ''' + S.name + '.' + O.name + 
    	''' FROM ' + S.name + '.' + O.name + 
    	' WHERE ' + C.name + ' = ' + '12345' + ' UNION ALL '
    from sys.objects as O
    	INNER JOIN sys.columns as C ON
    		O.object_id = C.object_id
    	INNER JOIN sys.schemas as S ON
    		O.schema_id = S.schema_id
    	INNER JOIN SYS.indexes as I ON
    		O.object_id = I.object_id
    WHERE O.type = 'U'	--User tables
    	AND C.user_type_id IN (56, 127) --INT and BIGINT
    	AND I.is_primary_key = 1
    Select the resulting column, copy it, and paste it in Oracle SQL Developer. Remove the last UNION ALL and run it. All the tables that have a primary key column with the value you provided will be listed.

    I am able to provide a unique Sequence number that it would be linked with
    You do mean that you know the primary key it will get? If not, remove the " AND I.is_primary_key = 1" from the SQL script.

    I made some assumptions to limit the number of columns that have to be checked:
    - column of a User table
    - INT or BIGINT data type
    - an index is defined on it
    - it is a primary key column
    Last edited by Wim; 10-01-13 at 13:13.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Wim View Post
    Run this script, after replacing 12345 with the unique Sequence number.
    Code:
    select 'SELECT ''' + S.name + '.' + O.name + 
    	''' FROM ' + S.name + '.' + O.name + 
    	' WHERE ' + C.name + ' = ' + '12345' + ' UNION ALL '
    from sys.objects as O
    	INNER JOIN sys.columns as C ON
    		O.object_id = C.object_id
    	INNER JOIN sys.schemas as S ON
    		O.schema_id = S.schema_id
    	INNER JOIN SYS.indexes as I ON
    		O.object_id = I.object_id
    WHERE O.type = 'U'	--User tables
    	AND C.user_type_id IN (56, 127) --INT and BIGINT
    	AND I.is_primary_key = 1
    Select the resulting column, copy it, and paste it in Oracle SQL Developer. Remove the last UNION ALL and run it. All the tables that have a primary key column with the value you provided will be listed.

    You do mean that you know the primary key it will get? If not, remove the " AND I.is_primary_key = 1" from the SQL script.

    I made some assumptions to limit the number of columns that have to be checked:
    - column of a User table
    - INT or BIGINT data type
    - an index is defined on it
    - it is a primary key column
    there is no sys.objects (i would also assume there is no sys.columns)
    To search for columns, I was using

    Code:
    FROM dbname.information_schema.columns
    and that works... but there is no
    Code:
    FROM dbname.information_schema.objects
    so replacing all the sys. didnt work.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by penfold1992 View Post
    im running the commands via Oracle SQL Developer... because the company wont let me use SSMS i guess...
    SSMS Express is free

    2012: http://www.microsoft.com/en-us/downl....aspx?id=29062
    When downloading just select

    ENU\x64\SQLManagementStudio_x64_ENU.exe
    or
    ENU\x86\SQLManagementStudio_x86_ENU.exe
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by gvee View Post
    SSMS Express is free

    2012: Download Microsoft® SQL Server® 2012 Express from Official Microsoft Download Center
    When downloading just select

    ENU\x64\SQLManagementStudio_x64_ENU.exe
    or
    ENU\x86\SQLManagementStudio_x86_ENU.exe
    I got it installed overnight but the error I got running the script was:
    Code:
    Msg 403, Level 16, State 2, Line 1
    Invalid operator for data type. Operator equals LIKE, type equals varchar.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    On the query with the comment
    Code:
    -- Like string matches
    Add the following line at the end:
    Code:
    AND    data_type NOT IN ('image')
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2012
    Posts
    63
    24 minutes in and its still running, I assume thats a good sign but im worried that its going to come back with no results because I should have included %'s in my string....

    what I entered was just a section of the string because the string contains line breaks and i dont know if SQL would interperate the line breaks properly so I selected just a small part of the string.... I should probably re run it with '% -string part- %'

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It will take a long time to run.
    It is querying against every column in every table looking for your search value.

    If you've specified a LIKE comparison:
    Code:
    SET @search_strings = 'L' -- E=Exact match, L=Like, N=Do not search.
    Then your search string will have a % appended and prepended to it.
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by penfold1992 View Post
    there is no sys.objects (i would also assume there is no sys.columns)
    TRy:
    Code:
    SELECT 'SELECT ''' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + '.' + C.COLUMN_NAME + 
    	''' FROM ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + 
    	' WHERE ' + C.COLUMN_NAME + ' = ' + '12345' + ' UNION ALL '
    FROM information_schema.TABLES as T
    	INNER JOIN information_schema.columns as C ON
    		T.TABLE_SCHEMA = C.TABLE_SCHEMA 
    		AND T.TABLE_NAME = C.TABLE_NAME
    WHERE T.TABLE_TYPE = 'BASE TABLE'
    	AND C.DATA_TYPE IN ('int', 'bigint')
    	AND C.IS_NULLABLE = 'NO'
    ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME
    But I see in other posts that you are searching for a string.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •