January 7, 2015 at 6:27 am
(Not mission critical) but would like to get to a solution.
Hopefully you can help with this, much googling has proved fruitless. I was hoping to compile a dictionary of comments from all my Procedures to assist newbies and other Database developers. Below is the script I am using to extract the comments information. The problem is iReports (jasper soft) or SSRS or other RTF in presents the TABS that currently exists differently or not at all.
An example of a comment block
/*
====================================================================================================================
YYYY/MM/DDOwnerYYYY/MM/DD-ReviewReleaseTfsDescription
----------------------------------------------------------------------------------------------------------
2012/11/07MarK Davis2012/11/21 ABV10.14dbause Institution instead of Universities
====================================================================================================================
*/
If you paste this in to SSMS it renders OK but anything else probably including your browser is problematic.
Having analysed the issue SSMS (SQL server) renders a tab ever 4 chars because that’s the default RTF editors changes the tab length to every 5 chars. Possibly a vi hangover.
Is there a way of replacing tab with spaces of 1,2 3,4 chars depending on position in line or a 5 char tab replacement solution (this may miss align comments in line or may another way of getting around this rendering issue.
I am aware in options | text editor|tabs there is insert spaces and adjust the tab chars but the proverbial horse has bolted.
Regards Mark
SELECT o.object_id,
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
o.name AS 'Stored Procedure Name',
SUBSTRING([definition],
LEN(LEFT([definition], CHARINDEX('/*', [definition]))) - 1,
LEN(LEFT([definition], CHARINDEX('*/', [definition]))) - LEN(LEFT([definition], CHARINDEX('/*', [definition]))) +3) AS HeaderComments,
o.create_date, o.modify_date, GETDATE() AS DateFileCreated
FROM
sys.sql_modules AS SM
INNER JOIN sys.objects AS o ON SM.object_id = o.object_id
WHERE
CHARINDEX ('/*',definition) > 0
AND
o.type = 'P';
January 7, 2015 at 7:22 am
Hi Mark,
Try wrapping the substring with a replace (CHAR(9) = TAB):
REPLACE(
SUBSTRING([definition],
LEN(LEFT([definition], CHARINDEX('/*', [definition]))) - 1,
LEN(LEFT([definition], CHARINDEX('*/', [definition]))) - LEN(LEFT([definition], CHARINDEX('/*', [definition]))) +3),
CHAR(9), ' ') -- four spaces, or use a case statement to choose 1,2,3,4,5 spaces depending on your requirements
AS HeaderComments,
January 7, 2015 at 7:52 am
Unfortunately this is not a fixed length data string replacement as it could be 1,2,3,4 chars depending the point at when the user pressed tab the replace will fix it at a particular length or string.
January 7, 2015 at 8:17 am
If you can expand on what rules would govern the number of spaces required we might be able to come up with a case statement that'll satisfy them.
January 7, 2015 at 9:03 am
position = if you look at the bottom of SSMS the bit with the Line column and Character the col stay the same at 13 for all the X's in example but the ch is variable
if tab chars is in position 1 then 4 i.e replace case (9) with 3 spaces
if tab chars is in position 2 then 4 i.e replace case (9) with 2 spaces
if tab chars is in position 3 then 4 i.e replace case (9) with 1 spaces
if tab chars is in position 5 then 8 i.e replace case (9) with 3 spaces
if tab chars is in position 6 then 8 i.e replace case (9) with 2 spaces
if tab chars is in position 7 then 8 i.e replace case (9) with 1 spaces
etc.............. upto 200 chars
This can be simply tested by creating a comment which has multiple rows that are tabulated by using tab button underneath data aligned with header
e.g
/*
YYYY/MM/DD Owner
---------- -------------
data1X
data2+X
data3++X
data4+++X
/*
This is aligned in ssms.
I think this case would be complex.
January 7, 2015 at 5:57 pm
/* Changes to provided code are :
1. move expression for "HeaderComments" into a cross apply, just so we can reference the resulting string in the call to DelimitedSplit8K
2. use DelimitedSplit8K to split the input string at every TAB
3. append enough spaces to pad out each partial string to a 4 char boundary
4. join the parts back together to make a new xml column "FixedHeaderComments"
5. retrieve the text() value of the xml as "HeaderComments"
*/
select
o.object_id
, @@SERVERNAME as ServerName
, DB_NAME() as DatabaseName
, o.name as 'Stored Procedure Name'
, ssc.FixedHeaderComments.value('(./text())[1]','varchar(8000)') as HeaderComments
, o.create_date
, o.modify_date
, GETDATE() as DateFileCreated
from sys.sql_modules as SM
cross apply (
select SUBSTRING([definition], LEN(LEFT([definition], CHARINDEX('/*', [definition]))) - 1, LEN(LEFT([definition], CHARINDEX('*/', [definition]))) - LEN(LEFT([definition], CHARINDEX('/*', [definition]))) + 3) as HeaderComments
) x
cross apply (
select Item + left(' ', 4 - len(Item) % 4) as [text()]
from dbo.DelimitedSplit8K(x.HeaderComments, char(9))
for xml path(''), type
) ssc(FixedHeaderComments)
inner join sys.objects as o
on SM.object_id = o.object_id
where CHARINDEX('/*', definition) > 0
and o.type = 'P';
You can find DelimitedSplit8K here : http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 8, 2015 at 1:47 am
Mister Magoo
The solution unfortunately does not work
from the example
/*
YYYY/MM/DDOwner
-----------------------
data1X
data2+X
data3++X
data4+++X
*/
Line 1 /*
Line 2 YYYY/MM/DD<tab>Owner
Line 3----------<tab>-------------
Line 4data1<tab><tab>X
Line 5data2+<tab><tab>X
Line 6data3++<tab><tab>X
Line 7data4+++<tab>X
Line 4*/
after running your script
Line 1 OK
Line 2 Owner now in position -1
Line 3 OK
Line 4 X now in position +1
Line 5 X now in position +1
Line 6 X now in position +1
Line 6 X now in position -3
Line 8 OK
January 8, 2015 at 7:07 am
I don't know how you are running/testing this, perhaps you could supply a readily runnable script like this:
declare @string varchar(8000);
set @string=' /*
YYYY/MM/DDOwner
-----------------------
data1X
data2+X
data3++X
data4+++X
*/';
select (select Item + left(' ', 4 - len(Item) % 4) as [text()]
from dbo.DelimitedSplit8K(@string, char(9))
for xml path(''),type).value('(./text())[1]','varchar(8000)')
/* RESULTS PASTED HERE
/*
YYYY/MM/DD Owner
---------- -------------
data1 X
data2+ X
data3++ X
data4+++ X
*/
*/
As you can see, the results I get are nothing like yours?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 8, 2015 at 10:16 am
Ok, now that I had a spare 10 minutes, I can see the problem was that you are manipulating multi-line text, and in my test I wasn't.
Handling the line feeds/carriage returns as well :
declare @string varchar(8000);
set @string=' /*
YYYY/MM/DDOwner
-----------------------
data1X
data2+X
data3++X
data4+++X
*/';
select (
select S2.line.value('(./text())[1]','varchar(8000)')+char(13)+char(10) as [text()]
from dbo.DelimitedSplit8K(replace(@string,char(10),''), char(13)) S1
cross apply (
select S2.Item + left(' ', 4 - len(S2.Item) % 4) as [text()]
from dbo.DelimitedSplit8K(S1.Item, char(9)) S2
for xml path(''),type) S2(line)
for xml path(''),type).value('(./text())[1]','varchar(8000)')
/* RESULTS PASTED HERE
/*
YYYY/MM/DD Owner
---------- -------------
data1 X
data2+ X
data3++ X
data4+++ X
*/
*/
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply