Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Analyzer Extended Expand / Collapse
Author
Message
Posted Friday, August 26, 2005 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 15, 2014 5:46 PM
Points: 38, Visits: 416
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/queryanalyzerextended.asp
Post #214522
Posted Thursday, September 29, 2005 1:04 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 24, 2014 12:40 AM
Points: 652, Visits: 1,894
Good idea, good code....terrible name for a stored procedure!
Post #224696
Posted Thursday, September 29, 2005 1:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:41 PM
Points: 2,693, Visits: 1,219

"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
Post #224714
Posted Thursday, September 29, 2005 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 29, 2006 6:10 AM
Points: 5, 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

Post #224766
Posted Thursday, September 29, 2005 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 29, 2006 6:10 AM
Points: 5, 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

Post #224775
Posted Thursday, September 29, 2005 7:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:41 PM
Points: 2,693, Visits: 1,219

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
Post #224805
Posted Thursday, September 29, 2005 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 16, 2011 12:32 PM
Points: 36, 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

Post #224829
Posted Thursday, September 29, 2005 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 2:14 PM
Points: 115, 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!

Post #224837
Posted Thursday, September 29, 2005 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:02 PM
Points: 10, Visits: 7
Can it be modified to show the user defined data types instead of the actual datatypes?
Post #224875
Posted Thursday, September 29, 2005 6:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 4:59 PM
Points: 79, Visits: 183

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
Post #225067
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse