SQLServerCentral / Discuss Content Posted by Yakov Shlafman / Article Discussions / Article Discussions by Author / Query Analyzer Extended / Latest Posts

RE: Query Analyzer Extended

Ram Ram — Fri, 29 Sep 2006 07:37:00 GMT

May be just give a try.

USE NorthWindGO

DECLARE @tblName VARCHAR(35)SELECT @tblName = 'Products'

SELECT [DeclareStmt] = RTRIM('DECLARE @'+C.[Name]+SPACE(CharLen-LEN(C.[Name]))+CHAR(9) +CASE WHEN T.[Name] IN ('SMALLDATETIME','DATATIME','TEXT','IMAGE','BIT','NTEXT','UNIQUEIDENTIFIER', 'BIGINT','INT','SMALLINT','TINYINT','MONEY','SMALLMONEY') THEN UPPER(T.[Name]) WHEN T.[Name] IN ('DECIMAL','NUMERIC') THEN UPPER(T.[Name]) + + '(' + CAST(C.[Length] AS VARCHAR(255)) + ') [' + CAST(C.[XPrec] AS VARCHAR(255)) + ',' + CAST(C.[XScale] AS VARCHAR(255)) + ']' ELSE UPPER(T.[Name]) + '(' + CAST(C.[Length] AS VARCHAR(255)) + ')' END), [InsertStmt] = RTRIM(QUOTENAME(C.[Name],'[]')+CASE WHEN C.ColId <> MaxLen.CId THEN ',' ELSE '' END), [ValuesStmt] = RTRIM('@'+C.[Name]+CASE WHEN C.ColId <> MaxLen.CId THEN ',' ELSE '' END), [UpdateStmt] = RTRIM(QUOTENAME(C.[Name],'[]')+SPACE(CharLen-LEN(C.[Name]))+CHAR(9)+' = @'+C.[Name] +CASE WHEN C.ColId <> MaxLen.CId THEN ',' ELSE '' END), [AssignStmt] = RTRIM('@'+C.[Name]+SPACE(CharLen-LEN(C.[Name]))+CHAR(9)+' = '+QUOTENAME(C.[Name],'[]')+ CASE WHEN C.ColId <> MaxLen.CId THEN ',' ELSE '' END), [DefaultValue] = SC.[Text], [ConstName] = Obj.[Name], Nullable = CASE WHEN C.[IsNullable] = 1 THEN 'Y' ELSE 'N' END, IsIdentity = CASE WHEN C.[Status]&0x80 = 0x80 THEN 'Y' ELSE 'N' END, [ProcParams] = RTRIM('@'+C.[Name]+SPACE(CharLen-LEN(C.[Name]))+CHAR(9) +CASE WHEN T.[Name] IN ('SMALLDATETIME','DATATIME','TEXT','IMAGE','BIT','NTEXT','UNIQUEIDENTIFIER', 'BIGINT','INT','SMALLINT','TINYINT','MONEY','SMALLMONEY') THEN UPPER(T.[Name]) WHEN T.[Name] IN ('DECIMAL','NUMERIC') THEN UPPER(T.[Name]) + + '(' + CAST(C.[Length] AS VARCHAR(255)) + ') [' + CAST(C.[XPrec] AS VARCHAR(255)) + ',' + CAST(C.[XScale] AS VARCHAR(255)) + ']' ELSE UPPER(T.[Name]) + '(' + CAST(C.[Length] AS VARCHAR(255))+')' END)+SPACE((16-LEN(CASE WHEN T.[Name] IN ('SMALLDATETIME','DATATIME','TEXT','IMAGE','BIT','NTEXT','UNIQUEIDENTIFIER', 'BIGINT','INT','SMALLINT','TINYINT','MONEY','SMALLMONEY') THEN UPPER(T.[Name]) WHEN T.[Name] IN ('DECIMAL','NUMERIC') THEN UPPER(T.[Name]) + + '(' + CAST(C.[Length] AS VARCHAR(255)) + ') [' + CAST(C.[XPrec] AS VARCHAR(255)) + ',' + CAST(C.[XScale] AS VARCHAR(255)) + ']' ELSE UPPER(T.[Name]) + '(' + CAST(C.[Length] AS VARCHAR(255)) + ')' END))+1)+CHAR(9)+CASE WHEN C.[IsNullable] = 1 THEN '= NULL' ELSE '' END+CASE WHEN C.ColId <> MaxLen.CId THEN ',' ELSE '' ENDFROM dbo.SysObjects AS O INNER JOIN dbo.SysColumns AS C ON O.[Id] = C.[Id] INNER JOIN dbo.SysTypes AS T ON C.[XType] = T.[XType] AND T.[XType] = T.[XUserType] INNER JOIN (SELECT [Id],MAX(LEN([Name]))+1 AS CharLen,MAX(ColId) AS CId FROM dbo.SysColumns GROUP BY [Id]) AS MaxLen ON MaxLen.[Id] = C.[Id] LEFT OUTER JOIN dbo.SysComments AS SC ON C.[CDefault] = SC.[Id] AND SC.[ColId] = 1 LEFT JOIN dbo.SysObjects AS Obj ON SC.[Id] = Obj.[Id] WHERE O.[Name] = @tblNameAND O.XType IN ('U','V')ORDER BY O.[Name],C.[ColId],C.[ColOrder]

Ram

RE: Query Analyzer Extended

Ram Ram — Fri, 29 Sep 2006 07:37:00 GMT

May be just give a try.

USE NorthWindGO

DECLARE @tblName VARCHAR(35)SELECT @tblName = 'Products'

Ram

RE: Query Analyzer Extended

EX Gianluca Sartori — Fri, 29 Sep 2006 01:31:00 GMT

Hi everybody,

I don't like short names for stored procedures, I prefer to know exactly what a sp is going to do just by looking at its name rather than having to open it and read the code. I'm a Java developer and I'm quite used to dealing with long named functions and methods...

By the way, I see no need for a sp to get table columns information, since I can query INFORMATION_SCHEMA.COLUMNS. If I have to generate code for a statement, I do it querying INFORMATION_SCHEMA, but it must be worth the while: I will never do it for a simple select / insert statement, since it is so easy and strightforward to write sql statements that it would take me much longer to remember the sp name rather than writing the statement by myself.

Regards

Gianluca Sartori

RE: Query Analyzer Extended

Yakov Shlafman — Wed, 02 Nov 2005 20:22:00 GMT

Hi Alan,

now the new, inproved version of the proc with

identity property is available from the same link. Format 1, type 1.

Thanks

Regards

Yakov

RE: Query Analyzer Extended

Yakov Shlafman — Sat, 15 Oct 2005 19:37:00 GMT

Hi Vernon,

Kalen Delaney wrote an exellent article "User-Defined Data Types" http://www.windowsitpro.com/SQLServer/Article/ArticleID/37427/37427.html.

She provides a stored procedure that shows all user defined datatypes.

Please let me know it this answers all your questions.

Thanks

Regard

Yakov

RE: Query Analyzer Extended

yakov shlafman-248344 — Wed, 05 Oct 2005 08:47:00 GMT

Hi Alan,

I will post a new, inproved version of the proc with

identity property.

Thanks

Regards

Yakov

RE: Query Analyzer Extended

Gary Johnson-259336 — Thu, 29 Sep 2005 18:59:00 GMT

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.

RE: Query Analyzer Extended

Vernon Reeve — Thu, 29 Sep 2005 09:41:00 GMT

Can it be modified to show the user defined data types instead of the actual datatypes?

RE: Query Analyzer Extended

Alan Armstrong — Thu, 29 Sep 2005 08:17:00 GMT

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!

RE: Query Analyzer Extended

Ted Crafton — Thu, 29 Sep 2005 08:07:00 GMT

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 masterif exists (select * from dbo.sysobjects where id = object_id(N'sp_ScriptHelper') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure sp_ScriptHelperGO

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOSET NOCOUNT ONGO

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

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

if @which = 1 or @which = 0Beginprint '/* 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 tblfieldsDEALLOCATE tblfieldsPrint ''Print ''Print ''ENDIf @which = '0' or @which ='2'Beginprint '/* Standard Variable Load from table ' + upper(@tblname) + '*/'Print ''Print ''Print ''Print 'Select 'Declare tblfields cursor for

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

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

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

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

CLOSE tblfieldsDEALLOCATE tblfieldsPrint ')'

Print ''Print ''Print ''End

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

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

CLOSE tblfieldsDEALLOCATE tblfieldsPrint '/* Where statement goes here */' EndSET NOCOUNT Off

SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOexec sp_MS_marksystemobject sp_ScriptHelperGO

RE: Query Analyzer Extended

philcart — Thu, 29 Sep 2005 07:28:00 GMT

For the identifier delimeters (brackets), in Query Analyzer, got Tools|Options and on the Script tab you can choose from,Version dependentNone[] -- 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.

RE: Query Analyzer Extended

yakov shlafman-248344 — Thu, 29 Sep 2005 06:41:00 GMT

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

RE: Query Analyzer Extended

yakov shlafman-248344 — Thu, 29 Sep 2005 06:29:00 GMT

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

RE: Query Analyzer Extended

philcart — Thu, 29 Sep 2005 01:52:00 GMT

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

RE: Query Analyzer Extended

David McKinney — Thu, 29 Sep 2005 01:04:00 GMT

Good idea, good code....terrible name for a stored procedure!

Query Analyzer Extended

Yakov Shlafman — Fri, 26 Aug 2005 11:36:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/queryanalyzerextended.asp