"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.

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

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

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.

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

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!

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.