Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query Analyzer Extended Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2005 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 29, 2006 6:10 AM
Points: 5, Visits: 1

Hi Alan,

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

identity property.

 

Thanks

Regards

Yakov

Post #226240
Posted Saturday, October 15, 2005 7:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:13 PM
Points: 33, Visits: 395

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

Post #229205
Posted Wednesday, November 2, 2005 8:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:13 PM
Points: 33, Visits: 395

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

Post #234731
Posted Friday, September 29, 2006 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:47 AM
Points: 8, Visits: 8

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

Post #312173
Posted Friday, September 29, 2006 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:57 PM
Points: 94, Visits: 192

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

 

 

 

Post #312248
Posted Friday, September 29, 2006 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:57 PM
Points: 94, Visits: 192

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

 

 

 

Post #312249
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse