SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Analyzer Extended


Query Analyzer Extended

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC Eights!
SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)

Group: General Forum Members
Points: 893 Visits: 516
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/queryanalyzerextended.asp
David McKinney
David McKinney
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5029 Visits: 2094
Good idea, good code....terrible name for a stored procedure!
philcart
philcart
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18515 Visits: 1441

"You should be aware - no matter what format you use to create a table Query Analyzer or Enterprise Manager will always return your code in First format"

This is not exactly correct. Take a look at the options that can be used for generating scripts.

CREATE TABLE dbo.Orders (
OrderID int IDENTITY (1, 1) NOT NULL ,
CustomerID nchar (5) NULL ,
EmployeeID int NULL ,
OrderDate datetime NULL ,
RequiredDate datetime NULL ,
ShippedDate datetime NULL ,
ShipVia int NULL ,
Freight money NULL CONSTRAINT DF_Orders_Freight DEFAULT (0),
ShipName nvarchar (40) NULL ,
ShipAddress nvarchar (60) NULL ,
ShipCity nvarchar (15) NULL ,
ShipRegion nvarchar (15) NULL ,
ShipPostalCode nvarchar (10) NULL ,
ShipCountry nvarchar (15) NULL
)

Not perfect, but more workable.

The two things I would like to have are,
1) Place the comma at the start. Like you have, this makes ad-hoc modification much easier.
2) When scripting select/insert/update statements, have the text wrap at a column you've previously specified, or preferably put each column on a new line like the create does.




Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
yakov shlafman-248344
yakov shlafman-248344
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 1

Hi David,

thanks for comments.

I agree with you... but... this is a procedure that you may use multiple times day after day and year after year ... and the name should be short (Cut typing if you can).

How should I call it? Any ideas for improvement?

Thanks

Regards

Yakov


yakov shlafman-248344
yakov shlafman-248344
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 1

Hi Phill,

thanks for your contribution.

I thought that Enterprise Manager or Query Analyzer always put column names in brackets (on both left and right sides). Your code example does not have brackets. How do you generate this code?

I am willing to improve my procedure. Could you please give me an example on your second comment. I may write one more procedure for this.

Do you create system procedures or functions from user written procedures you like?

Thanks

Regards

Yakov


philcart
philcart
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18515 Visits: 1441

For the identifier delimeters (brackets), in Query Analyzer, got Tools|Options and on the Script tab you can choose from,
Version dependent
None
[] -- 7.0 compatible
" " -- 6.5 compatible

For point two, I wasn't really expecting you to do anything with your procedure. It was more to do with how things are scripted in query analyzer.

We have a couple of tables that have way too many fields. Most of my adhoc queries on these tables use a majority of the fields in the table and it always seems to be the last few that I end up removing. So I'm constantly finding myself scrolling across to find the fields to delete.

All my user written procedures stay as user procedures whether they perform system functions or not. I do have one stored procedure I put together that generate insert/update/delete procedures for our datawarehouse. I pass in a table name and it does the rest.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Ted Crafton
Ted Crafton
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 5

I wrote a procedure that basically does the same thing. It was not as robust as yours. We do a lot of stored procedures and triggers on big tables.

And before everyone complains about the use of cursors, it was a quick and dirty work around for a problem.

Use master
if exists (select * from dbo.sysobjects where id = object_id(N'sp_ScriptHelper') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_ScriptHelper
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO

CREATE PROCEDURE sp_ScriptHelper
@tblName nvarchar(50)='',
@which int = '0',
@Prefix nvarchar(5)=''

AS


Declare @FldName as nvarchar(200)
Declare @fldtype as nvarchar(30)
Declare @fldlgth as int


if @which = 1 or @which = 0
Begin
print '/* Standard Declares for table ' + upper(@tblname) + '*/'
Declare tblfields cursor for

SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
BEGIN
if not @fldlgth is null
if not @Prefix is null
Print 'Declare @' + @Prefix + @fldname + ' as ' + @fldtype + ' (' + cast(@fldlgth as nvarchar(10)) + ')'
else
Print 'Declare @' + @fldname + ' as ' + @fldtype + ' (' + cast(@fldlgth as nvarchar(10)) + ')'
else
if not @Prefix is null
Print 'Declare @' + @Prefix + @fldname + ' as ' + @fldtype
else
Print 'Declare @' + @fldname + ' as ' + @fldtype
END

FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop

CLOSE tblfields
DEALLOCATE tblfields
Print ''
Print ''
Print ''
END
If @which = '0' or @which ='2'
Begin
print '/* Standard Variable Load from table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'Select '
Declare tblfields cursor for

SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
if not @Prefix is null
Print '@' + @Prefix + @fldname + ' = ' + @fldname + ','
else
Print '@' + @fldname + ' = ' + @fldname + ','

FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop

CLOSE tblfields
DEALLOCATE tblfields
Print 'FROM ' + upper(@tblname)
Print '/* WHERE clause goes here */'
Print ''
Print ''
Print ''
End

IF @which ='0' or @which = '3'
Begin
print '/* Standard Insert for table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'INSERT INTO ' + Upper(@tblname) + '('
Declare tblfields cursor for

SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN

if @fldtype<> 'timestamp'

if not @Prefix is null
Print '@' + @Prefix + @fldname + ','
else
Print '@' + @fldname + ','
else
Print 'DEFAULT'

FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop

CLOSE tblfields
DEALLOCATE tblfields
Print ')'

Print ''
Print ''
Print ''
End

if @which = '0' or @which= '4'
Begin
print '/* Standard Update for table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'Update ' + upper(@tblname)
Print 'SET'
Declare tblfields cursor for

SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN

if @fldtype<> 'timestamp'

if not @Prefix is null
Print @fldname + ' = @' + @Prefix + @fldname + ','
else
Print @fldname + ' = @' + @fldname + ','
else
Print @fldname + ' = DEFAULT'

FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop

CLOSE tblfields
DEALLOCATE tblfields
Print '/* Where statement goes here */'
End
SET NOCOUNT Off

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec sp_MS_marksystemobject sp_ScriptHelper
GO


Alan Armstrong
Alan Armstrong
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 19

but you cannot see if a column has the identity property set and seed and increment values.

I prefer your format, but the very thing you are talking about is something your procedure doesn't do!


Vernon Reeve
Vernon Reeve
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 7
Can it be modified to show the user defined data types instead of the actual datatypes?
Gary Johnson-259336
Gary Johnson-259336
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 184

A long time ago I came across an sp called sp_select on the old site. I have since taken that sp and modified it, munged it so that now I have sp_select, sp_insert, sp_insertselect, and sp_declare. Since I rarely need to do deletes and most updates are pretty specific I never really used the helper sp for them. If I get a chance I will upload them. And yes, I too put the comma at the front. My sp_declare will use UDT's if they exist (I hate those things!) as well.

Basically with the above sprocs I could nearly have the database spit out a sproc for doing selects on a table

Also, it would be very simple to modify the sp_declare I have to show the field is an identity.



Gary Johnson
Sr Database Engineer
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