SQL statement to find a particular column within all tables

I am looking for a query that will allow me to find all instances of a 
particular column within all tables so I know where they are all located.

Does anyone have such a query they would be willing to share?

Thank you.

0
Pam1 (264)
7/28/2008 1:32:02 PM
greatplains 29623 articles. 6 followers. Follow

2 Replies
487 Views

Similar Articles

[PageSpeed] 10

Pam,

I posted this query a few months aback on my blog 
(http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt:

select distinct rtrim(objs.name)
from syscolumns cols
inner join sysobjects objs on (cols.id = objs.id)
inner join sysindexes indx on (cols.id = indx.id)
where (cols.name = 'ACTINDX') and (objs.xtype = 'U') and (indx.rowcnt <> 0)

I have plenty of scripts on my blog that may be useful to you. 

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


"Pam" wrote:

> I am looking for a query that will allow me to find all instances of a 
> particular column within all tables so I know where they are all located.
> 
> Does anyone have such a query they would be willing to share?
> 
> Thank you.
> 
0
MarianoGomez (3440)
7/28/2008 1:58:01 PM
Mariano,

Thank you so much for the query - helpful as ever.  
I guess I didn't know about your blog - I will be sure to check it out!

Thank you

"Mariano Gomez" wrote:

> Pam,
> 
> I posted this query a few months aback on my blog 
> (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt:
> 
> select distinct rtrim(objs.name)
> from syscolumns cols
> inner join sysobjects objs on (cols.id = objs.id)
> inner join sysindexes indx on (cols.id = indx.id)
> where (cols.name = 'ACTINDX') and (objs.xtype = 'U') and (indx.rowcnt <> 0)
> 
> I have plenty of scripts on my blog that may be useful to you. 
> 
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP
> Maximum Global Business, LLC
> http://www.maximumglobalbusiness.com
> 
> 
> "Pam" wrote:
> 
> > I am looking for a query that will allow me to find all instances of a 
> > particular column within all tables so I know where they are all located.
> > 
> > Does anyone have such a query they would be willing to share?
> > 
> > Thank you.
> > 
0
Pam1 (264)
7/28/2008 2:10:01 PM
Reply:

Similar Artilces:

want to do piovt table, where rows over 100,000
Hi.. i have around 100,000 rows of data... of cousres the worksheet i can only put 66000 or so rows per sheet. i want to do a pivot table that combines the two sheets. how can i do this? thank -- Message posted from http://www.ExcelForum.com Hi if you have that many data I would suggest you store this data in a real database (e.g. MS Access). With Excel's pivot table wizard you can then access this database as datasource. Another way would be to split the data in two worksheets and within the pivot table wizard choose the 'non contigenous ranges' for the data source -- Reg...

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Create Login with T-SQL
Hello, I am creating, using T-SQL, a database with its file groups, tables, constraints, etc. Can I also create, using T-SQL, a Login with Username and Password to access that database? Usually, I go to Security > Logins of SSMS to manually create the login. However, if I could do the same using T-SQL I would have it all made automatically. Thanks, Miguel EVERYTHING that the GUI does winds up as tsql executions on the server. :-) Most of them you can generate a script for using the Script menu in the upper left of the current dialog window. This is a great way to ...

Emails don't find contacts in restored Outlook
My hard drive burned up, so I replaced it, reinstalled, and imported everything into Outlook. The contacts are there if you click contacts. However, when sending emails, there is no way to connect to my contacts. It's like they are invisible. You never import Outlook data to restore it. You just open the Outlook data file. When you do, you will be able to configure your address book view to display your Contacts the same way as always: http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 -- Russ Valentine "Mike Row Soft" <MikeR...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

Show / Hide items in Pivot Table as required
Hi I am using MS Excel 2003 and need a macro code for show only my selected items only in a Pivot Table. Query: One excel table empstatus.xls in which fields are Employee No , Department, Status, other fields. In status field items are Blank, Leave, Left, Resigned. I wanted to show only blank & leave based on when I’m selecting a particular month in my pivot table. So is there any code to hide all data items of status and only show blank & leave. Please suggest. -- ------------------------------ Thanks Nitesh ------------------------------ ...

Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my boss loves tabs for navigation). Each page has a list box based on a category of training events that members of my office attend and the box is based on a query that selects the training events for the tab page's respective category. I select an item from the list, click a button, and go to a form with a text box showing the name of the training event I previously selected. On that form, I want to select mutiple names of office members from a list box (already created and source is the Office Roster table) and then c...

Someone must have asked this before but I can't find it!
Being a relative Excel newcomer I,m not sure of all the terminology so may have used the wrong terms when searching. What I am trying to do is create a worksheet in which a cell shows one of 3 values (LOW,AVERAGE or HIGH) according to the value in the cell to the left of it, which itself is the sum of the 2 preceding cells. To be more specific, cell D6 is Men, E6 is Women and F6 is"=SUM(D6,E6)". I then want G6 to show "LOW" if F6 is less than 5, "AVERAGE" if it is between 5 and 20, and "HIGH" if it is over 20. If anyone understands the question and can h...