Click here to Skip to main content
14,733,599 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI ,

I have a table containing

Columns --> d1 d2 d3 d4 .......

Values --> HL P WO HL ............

TABLE VALUES MAY VARY .BUT I HAVE A STRING HL

how to compare a string value to all the columns in a single table .and get column count dynamically

FOR EX: FOR HL STRING COLUMN COUNT IS 2.

HOW TO GET THIS DYNAMICALLY
Posted

pls try this

replace the 'yourCheckString' with the substring you are searching in the tables, here this script will check all the tables for the substring and retruns the column count which contains the matched string.

DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @BuildCondition VARCHAR(200)
DECLARE @BuildSpecialChars VARCHAR(200)
DECLARE @CheckSubstring VARCHAR(200)
DECLARE @sql VARCHAR(500)

SET @CheckSubstring='yourCheckString';
SET @BuildSpecialChars ='PATINDEX(''%'+@CheckSubstring+'%'',[somecolumn])>0'

if OBJECT_ID('tempdb..#t')is not null
  Drop table #t
  
CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64),
    specialchar nvarchar(64)
)

DECLARE TempTables CURSOR
FOR
	SELECT o.name, c.name
	FROM syscolumns c
		INNER JOIN sysobjects o ON c.id = o.id
	WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
	ORDER BY o.name, c.name
OPEN TempTables
FETCH NEXT FROM TempTables
INTO @table, @column
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @BuildCondition='';
			SET @BuildCondition=REPLACE(@BuildSpecialChars,'somecolumn',@column);

			SET @sql = 'IF EXISTS(SELECT NULL FROM ' + @table + ' '
			SET @sql = @sql + 'WHERE '+@BuildCondition+') '
			SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
			SET @sql = @sql + @column + ''','''
			SET @sql = @sql + ''')'
			EXEC(@sql)
		
		FETCH NEXT FROM TempTables
		INTO @table, @column
	END
CLOSE TempTables
DEALLOCATE TempTables

SELECT count(*) FROM #t


hope this will help...
   
v2
try this will work...
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
declare @qry as nvarchar(max);
select @cols = STUFF((SELECT distinct 
           ' sum (case when ' + column_name+'='+''''+'HL'+''''+'then 1 else 0 end)+'
               FROM information_schema.columns where table_name='YourTableName'
               FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)') 
               ,1,1,'')
SET @cols = LEFT(@cols, LEN(@cols) - 1)
set @qry='select '+ @cols + ' as Column_Count from YourTableName'
execute(@qry);
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900