Post reply

Identify Columns and/or Tables from records

  • March 3, 2015 at 4:04 am

    #295009

    Hi guys, I'm new to the forum and to SQL. I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

    In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

    Customer ID CustomerAddress Phone Email Account Balance Currency

    js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD

    jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP

    mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

    In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

    As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

    I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possble to do via a query?

    I hope I've given enough info and someone can help me.

    Cheers in advance!

  • March 3, 2015 at 6:44 am

    #1781338

    while it's possible to search every column in the database for a specific value with a big old nasty slow cursor that cripples other users performance while you are using it, based on dynamic SQL, i wouldn't bother.

    you also need some hefty permissions, which you might not have.

    you'd be a lot better of going directly to the source of the report...don't try and find a back door...ask other developers, or simply open the rdl(ssrs report?) and get the definition being used.

    you can even read the Content of the rdl report if you can't find the source code:

    select convert(varchar(max),CONVERT(varbinary(max),Content))As StrContent,*

    from ReportServer.dbo.Catalog

    after that, i'd look directly at tables; just looking at the data and the name of the report gives you a good idea that you need to find a table named "customer" or some directive of it; find the table, and look at the table names; then maybe do a select top 100 * from Customers to confirm your suspicions

    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!

  • March 3, 2015 at 7:21 am

    #1781346

    Many thanks bud, that'll save me some time !

Viewing 3 posts - 1 through 2 (of 2 total)

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