Please try:
--Check whether there is an object with the same name in the database, delete if there is
IF(exists(SELECT * FROM sysobjects WHERE name='yourtable'))
DROP TABLE yourtable;
IF(exists(SELECT * FROM sysobjects WHERE name='Fuzzy_search1'))
DROP PROC Fuzzy_search1;
--Create test tables and stored procedures
CREATE TABLE yourtable(INFO_aa int,INFO_bb int,INFO_cc int,
DTL_aa int,DTL_bb int,DTL_cc int);
CREATE PROC Fuzzy_search1(@Dup NVARCHAR(max)='INFO')
AS
DECLARE @sql NVARCHAR(MAX)
DECLARE @col NVARCHAR(MAX)
SELECT @col =STUFF(( SELECT ',' + t.name FROM
(SELECT name
FROM syscolumns
WHERE id=(SELECT MAX(id)
FROM sysobjects
WHERE xtype = 'u'
AND name = 'yourtable')) t
WHERE reverse(substring(reverse(t.name),charindex('_',reverse(t.name)) +1,500))=@Dup
FOR XML PATH('')
), 1, 1, '')
SET @sql = 'SELECT ' + @col + ' FROM yourtable';
EXEC(@sql);
--Execute stored procedure
EXEC Fuzzy_search1 'INFO'
Output:
IF(exists(SELECT * FROM sysobjects WHERE name='Fuzzy_search2'))
DROP PROC Fuzzy_search2;
CREATE PROC Fuzzy_search2(@Dup NVARCHAR(max)='DTL')
AS
DECLARE @sql NVARCHAR(MAX)
DECLARE @col NVARCHAR(MAX)
SELECT @col =STUFF(( SELECT ',' + t.name FROM
(SELECT name
FROM syscolumns
WHERE id=(SELECT MAX(id)
FROM sysobjects
WHERE xtype = 'u'
AND name = 'yourtable')) t
WHERE reverse(substring(reverse(t.name),charindex('_',reverse(t.name)) +1,500))=@Dup
FOR XML PATH('')
), 1, 1, '')
SET @sql = 'SELECT ' + @col + ' FROM yourtable';
EXEC(@sql);
EXEC Fuzzy_search2 'DTL'
DROP TABLE yourtable
DROP PROC Fuzzy_search1
DROP PROC Fuzzy_search2
Output:
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.