Finding column. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Finding column.

Does anybody know how would I go through all the tables and columns in order to find columns that have value ‘Optic’ in them? Thanks.

Select SO.Name From SysColumns SC
Inner Join SysObjects SO on SO.ID = SC.ID
Where SC.name = @ColName http://vyaskn.tripod.com/sql_server_search_and_replace.htm for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for your respond but I didn’t explain good enough what I needed.
I would like to find not the name of the
column, but the columns that have specific data in it.
How would I do it? Thanks.
Alex, The following makes use of a cursor (for which I will get some stick, no doubt) and it will take some time if your database is large. i.e. not "sql-server-performance" It will return tablename, fieldname, and then the rows containing that data. Regards, Robert. –declare your variables declare @cnt1 varchar(6000), @cnt2 varchar(6000), @sel1 varchar(6000), @sql varchar(6000), @count int
declare @str varchar(20), @fld varchar(50), @tbl varchar(50)
–declare @type varchar(20) — define the text string you are interested in SET @str = ‘Optic’ –Get a list of user tables and columns within them that are string fields DECLARE cols CURSOR FOR
SELECT distinct t.[name] as tbl, c.[name] as fld –, y.[name] as type
FROM sysobjects t
INNER JOIN syscolumns c
ON t.id = c.id
INNER JOIN systypes y
ON c.type = y.type
WHERE t.type = ‘u’
AND y.name in (‘sql_variant’, ‘nchar’, ‘nvarchar’, ‘varchar’, ‘char’)
— Go through each table and column in turn and select the data. OPEN cols
FETCH NEXT
FROM cols
INTO @tbl, @fld
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sel1 = ‘SELECT ‘ + ”” + @tbl + ”” + ‘, ‘ + ”” + @fld + ”” + ‘, * FROM ‘
SET @cnt1 = ‘SELECT COUNT(*) FROM ‘
SET @sql = @tbl + ‘ WHERE ‘ + @fld + ‘ = ‘ + ”” + @str + ””
SET @sel1 = @sel1 + @sql
SET @cnt1 = @cnt1 + @sql
SET @cnt2 = ‘DECLARE row_count SCROLL CURSOR FOR ‘ + @cnt1
EXEC(@cnt2)
OPEN row_count
FETCH FIRST FROM row_count INTO @count
CLOSE row_count
DEALLOCATE row_count
IF @count > 0
BEGIN
EXEC(@sel1)
END
FETCH NEXT
FROM cols
INTO @tbl, @fld
END CLOSE cols
DEALLOCATE cols

You can also use the syntax
SELECT …
FROM my_table
WHERE ‘Optic’ IN (column_1, column_2…
Here is an example of how you can get the column name. I assumed char/varchar, but you can change it as you wish. Obviously we are talking tabla scan.
declare @columns varchar(8000)
, @sql nvarchar(4000) select @columns = COALESCE(@columns + ‘,’, ”) + column_name
from information_schema.columns
where table_name = ‘authors’
and data_type in (‘varchar’, ‘char’) — looking for all columns containing the string ‘ann’ select @sql = ‘select * from authors where ”ann” in (‘ + @columns + ‘)’
exec sp_executesql @sql

The link I’ve provided above has the required task too. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya, the link you providedhttp://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
is usefull for pubs database. How do I modified it to run for my database? Thanks.
I believe you missed this line in the link :
Create this procedure in the required database and here is how you run it:… Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Neat trick with the IN () clause. However I would guess that the query only returns rows where the entire field matches the search value, and wildcards cannot be used, right?
I created this procedure in my db. I run it and it returns nothing, even though I know that the value is in there.
In pubs the same procedure gives me what is needed.
????????????
]]>