0

We got a requirement in our application like this. To develop a powershell script to update computername in web.config file and in database tables.

This script will be executed if application was already installed but user has to change the computer name. [so even if machine name changed , our application which was installed will not break if he executed the script. No need to uninstall and reinstall the product]

Now lot of tables contains field which has computername in it. For ex. columns like URL, BaseURL contains computername then remaining texts.

I have to find the table names which has column value computername (like column names URL and baseURL contains machine name.For example. AB2S481V0:80/Project/Default.aspx AB2S481V0 is the computername ).

Finding the list by manually looking after each table is burdensome as many tables there. Is there any way to find the list of table names if i pass databasename and column substring(not the entire column name)

Samselvaprabu
  • 16,830
  • 32
  • 144
  • 230
  • Probably this might help you http://stackoverflow.com/questions/4197299/sql-server-2008-find-which-tables-contain-specific-text – Ankit Jul 02 '12 at 09:08

2 Answers2

0

I often search tables by column name, using this query:

USE MYDB
SELECT t.name, c.name
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name LIKE '%[my_serach_pattern]%'

The object_id refers to the table id inside the sys.tables (i.e. the object_id inside the sys.objects table).

Have a look in MSDN for further info.

Alberto De Caro
  • 5,147
  • 9
  • 47
  • 73
0
Select a.name as  [Table Name],b.name as [Column Name]
from sys.tables a
Join sys.columns b on a.object_id = b.object_id
where b.name like '%ColumnName%'
Asif
  • 2,657
  • 19
  • 25