Technical Article

syscolumns names

,

Sometimes you need to manipulate a transfer data from a souce database

to another; if you need to exclude some columns from the table list (you got them as comma delimited string list dinamically) you have to cast the string column name as sysname type

you can do it with this function, but you can use for whatever table type you want to retrieve

Just pass a list as string comma separated and receive a table of that list.

Adjust the return type at your convinience.

-- =============================================
-- Author:Bernabé Díaz
-- Create date: 12/24/2008
-- Description:returns an array of sysname from string filter
-- =============================================
CREATE FUNCTION [dbo].[Udf_TABLECOLUMNLIST]
( 
@strDataSet VARCHAR(4000)
) 
RETURNS @DST TABLE
(
x SYSNAME
) 
AS

BEGIN
DECLARE @Idxb INT

SET @Idxb=CHARINDEX(',',@strDataSet,1)

IF @Idxb=0
BEGIN
INSERT INTO @DST
SELECT CAST(@strDataSet AS SYSNAME)
RETURN 
END
ELSE
BEGIN
WHILE CHARINDEX(',',@strDataSet,1)<>0
BEGIN

INSERT INTO @DST
SELECT CAST(SUBSTRING(@strDataSet,1,@Idxb-1) AS SYSNAME)

SELECT @strDataSet=LTRIM(RTRIM(SUBSTRING(@strDataSet,@Idxb+1,LEN(@strDataSet)-@Idxb)))
SET @Idxb=CHARINDEX(',',@strDataSet,1)

CONTINUE
END
END

INSERT INTO @DST
SELECT CAST(LTRIM(RTRIM(@strDataSet)) AS SYSNAME)

RETURN 

END

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating