UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mark Start And End Block, Office 2013    
 
   
WildBird
post Jan 5 2020, 11:15 PM
Post#1


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


Hi,

We are doing some documenting stuff on a SQL Server database - I know it would be good to have a OTS system, but we dont and have to roll our own.

Anyway, I have all the text from the stored procedures in Excel. Column A has stored pro name, and B has the text.

What I want to do is be able to search for a particular string (such as a table name) and highlight any lines that this may appear in.

Easy enough to do an instr (or match whatever the formula is in Excel), the issue is I dont want commented out lines. SQL Server can be commented out a few ways, '--' or a block '/*......*/'

I have code that will search a cell for a string, and if it isnt commented out, then return a 1.

What I am looking at is how to mark the start of a comment block ('/*), then end ('*/') and the ones in between.

CODE
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************
****************
Object:      Procedure PaymentStuff
Description: This procedure returns the Payment Stuff for a given period selected
Created By:  Thaddeus Venture and Brock Sampson
Created On:  4/10/2019
History:     Initial Release
********************************************************************************
****************/
CREATE PROCEDURE [PaymentStuff]


For this, the first 2 lines would be blank, as well as the last, but the lines in between I would want to be marked.

I can use helper columns, so can get if it starts with '/*' return a 1 in column c. Column D if it ends in '*/', return a 1, so C3 = 1, D9 = 1, everything else blank or 0. I need a way to have E3:E9 showing 1.

Hopefully this makes sense. First day back at work, I could barely remember what floor I worked on, couldnt remember my username, nor my password...

Hope everyone else had great holidays and back working competently now :-)



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
nvogel
post Jan 6 2020, 08:33 AM
Post#2



Posts: 1,069
Joined: 26-January 14
From: London, UK


You can search your code directly within SQL Server: just query the sys.sql_modules table.

You could also search in your IDE / source control tool. SQL Server Management Studio has a "Find in Files" option that will search an entire project. Excel is obviously not a viable source control tool and if you don't already have a source control tool then I suggest you should get one. The best and most widely used tools are open source or free to use, e.g. Git / Subversion, MS SQL Server Data Tools.
Go to the top of the page
 
WildBird
post Jan 6 2020, 03:34 PM
Post#3


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


Hi,

My team isnt the DBA team, they have source control etc. We just have to document it a bit, so we can see relationships etc (I am talking about what stored procedures affect what tables, and what jobs call what stored proc and what triggers etc. System doesnt change much at all, thankfully, but we have been tasked with providing some documentation, that is not technical i.e. in Excel or Word etc. Obviously I want it automated as much as possible, and we can get most of it, but I am just wanting to be able to exclude commented lines). Other words, ignore the details of what the text is about, and just want to be able to mark a column C as something if column A is 1, and then change when Column B is 1.

E.g required output something like
0 0 Blank
1 0 True
0 0 True
0 0 True
0 1 True
0 0 Blank

Cheers!

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jan 6 2020, 07:57 PM
Post#4


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


Aarrggh, more we looked at it, more we realised what I had wasnt going to cover all scenarios. I ended up with something with a few helper columns, but when I was discussing with a colleague, we realised that comment blocks dont have to start at the start of the line, can be anywhere within the line. The way SQL allows it is the problem.

Anyway, hoping we have or can get a off the shelf system like red gate to do this.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jan 6 2020, 08:14 PM
Post#5



Posts: 3,550
Joined: 27-February 09



You could find all the folks on Twitter that work for Redgate... there are a BUNCH... (Kendra Little, Grant Fritchey, Steve Jones....) that might be able to help... or call them. or just post a question on there.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 02:48 AM