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.
Regard
now the new, inproved version of the proc with
identity property is available from the same link. Format 1, type 1.
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.
Gianluca Sartori
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
