TallyCTE for CSV Values

DECLARE @MfrCodes VARCHAR(8000)


SELECT @MfrCodes = '1DEF,ABS,ACI,ACL,AEA' ;



SELECT @MfrCodes = ',' + @MfrCodes + ',' ;









WITH cteTally

AS (

SELECT ROW_NUMBER() OVER ( ORDER BY o.id ) AS N

FROM master.sys.sysobjects o

CROSS JOIN master.sys.sysobjects o2

),







cteSplit

AS

(

SELECT SUBSTRING(@MfrCodes, N + 1,

CHARINDEX(',', @MfrCodes, N + 1) - N - 1) AS VALUE,N,CHARINDEX(',', @MfrCodes, N + 1) - N - 1 AS VALUE2

FROM cteTally

WHERE N < LEN(@MfrCodes)

AND SUBSTRING(@MfrCodes, N, 1) = ','

)

SELECT *,@MfrCodes

FROM cteSplit

Recursive CTE to Generate Sequnce Numbers upto any limit

WITH CTE (num) AS (

SELECT 1

UNION ALL

SELECT num+1

FROM CTE

WHERE num<90000

)

SELECT DISTINCT num FROM CTE c1

OPTION (MAXRECURSION 0)

Find a specific column in all database tables

following query can be used to find a specific column in all database tables

select table_name from information_schema.columns where column_name = 'Inactive'