Query Analyzer Extended

  • 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