September 29, 2006 at 7:37 am
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 post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply