Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


syscolumns names


syscolumns names

Author
Message
diaz.bernabe
diaz.bernabe
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 194
Comments posted to this topic are about the item syscolumns names
Arto Ahlstedt
Arto Ahlstedt
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 802
Considering the simplicity and apparent history of working reliably in several places, I found a surprisingly large number of finer points in the submitted code. The asymmetry in trimming was what bothered me the most.

I removed some fluff such as extra CONTINUE which was exactly as useful here as it is in all the silly WHILE examples in BOL (might be why it had succeeded in sneaking into the code). Also, a set of one members is now handled like any other last element and not as a special case (with its own trimming issue in the original code). These minor issues demonstrate just how dangerous trusted, working, legacy code can be. This looks like it has been used during several years, a workhorse that has seldom let anyone down. And then comes a newbie with his edge cases Smile

My version:

CREATE FUNCTION [dbo].[Udf_TABLECOLUMNLIST]
(
@strDataSet VARCHAR(4000)
)
RETURNS @DST TABLE
(
x SYSNAME
)
AS

BEGIN
DECLARE @Idxb INT

IF @strDataSet IS NULL RETURN -- a rowset having zero rows

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

WHILE @Idxb<>0
BEGIN
INSERT INTO @DST
SELECT CAST(LTRIM(RTRIM(SUBSTRING(@strDataSet,1,@Idxb-1))) AS SYSNAME)

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

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

RETURN

END
GO

SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' a , b ')
UNION ALL
SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' c ')



I added the function to my SQL toolbox. Thanks.
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
No need for a while loop to split delimited strings. See Jeff Modens Tally table article, linked to in my sig below, for some great tips on avoiding while loops in this, and many other situations.

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

richard.jereb
richard.jereb
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 62
This is what fascinates me whenever working with SQL - there are always dozens of alternatives ;-)

I analyzed recently some ways to parse a string to a table for performance - with long strings the methods tend to become quite expensive.

The winner from CPU point of view was the XML method, this goes like this:

declare @strDataSet as varchar(max);
declare @delimiter as varchar(10);
set @strDataSet='A,B,C,D,E';
set @delimiter =',';

declare @xml as xml;
set @xml = cast(('<X>'+replace(@strDataSet,@delimiter ,'</X><X>')+'</X>') as xml);
select N.value('.', 'sysname') as x from @xml.nodes('X') as T(N);



Here some performance results for a string with 2361 delimited elements...

1: XML (see above) - SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 25 ms.
(2361 row(s) affected)

2: #Temp (Bernabe's way) - SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 278 ms.
(2361 row(s) affected)

3: CTE - SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 467 ms.
Msg 530, Level 16, State 1, Line 19
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

As you see, CTE has limitations and is rather expensive - but that XML is that much faster than stirng functions in a loop, was a surprise to me...

Cheers, Richard.
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
Richard,

Try out Jeffs tally table method, if you haven't already, you should find it's even faster.
I got the following times on a 9000 element source string.

 ------------ XML Method  ------------

(9000 row(s) affected)

SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 228 ms.


------------ Tally table method ------------

(9000 row(s) affected)

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 159 ms.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

richard.jereb
richard.jereb
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 62
Thanx for your hint, Nigel,

the Tally Table method was new to me, but I like this - sleek and fast!

Performance differences are varying - I get slightly different values with every execution - but generally the XML and the Tally Methods are really performant.

If the tally table must be created first than probably there is not much difference, but...

If you happen to have your numbers table somewhere in the DB - the tally is indeed even faster than any other solution I tried!

Thank you again for your valuable contribution!

Cheers, Richard.
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
Richard,

No problem at all.

There's actually a big thread (almost 500 posts) here discussing the various methods of splitting strings that's worth looking at.

If you have the time, that is :-)

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

diaz.bernabe
diaz.bernabe
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 194
I like the xml way, then I worked on that version
Here is another way thanks to this forum. It is a generalize version.
I prepared this version (my original did not used XML)
By being helped by forums feedback from SQL central
Forward it to yours peers.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bernabé Díaz
-- Create date: 04/08/2010
-- Description: To return a list from an string
-- =============================================

CREATE PROCEDURE Usp_GetTableList
@strDataSet AS VARCHAR(max)
,@delimiter AS VARCHAR(10)=','
,@Rt AS VARCHAR(128)='SYSNAME'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @STR VARCHAR(MAX);
SET @strDataSet=ISNULL(@strDataSet,'')
SET @STR=
'
DECLARE @xml AS XML;
SET @xml = CAST((''<X>''+REPLACE('''+@strDataSet+''','''+@delimiter+''' ,''</X><X>'')+''</X>'') AS XML);
SELECT N.value(''.'', '''+@Rt+''') AS x FROM @xml.nodes(''X'') AS MyTable(N);
'
EXEC(@STR)
END
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8664 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search