Query Analyzer Extended
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/queryanalyzerextended.asp
-
Good idea, good code....terrible name for a stored procedure!
-
"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.
--------------------
Colt 45 - the original point and click interface
-
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.
--------------------
Colt 45 - the original point and click interface -
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!
-
Can it be modified to show the user defined data types instead of the actual datatypes?
-
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 -
Hi Alan,
I will post a new, inproved version of the proc with
identity property.
Thanks
Regards
Yakov
-
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
-
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
-
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
-
May be just give a try.
USE NorthWind
GO
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 '' END
FROM 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] = @tblName
AND O.XType IN ('U','V')
ORDER BY O.[Name],C.[ColId],C.[ColOrder]
Ram
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply