Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
Possibly useful VIEW |
Tue, Dec 2 2014 9:34 AM | Permanent Link |
Adam Brett Orixa Systems | This just took me about 30 min to write and has some general usefulness, so I thought I would share it:
It is a view which concatenates the contents of the TableColumns data table into a single "FieldDescription" CREATE VIEW TableColumnSummary AS SELECT TableName, CAST(OrdinalPos as VARCHAR( 10)) + '. ' + Name + ' (' + UPPER(Type) + IF("Length" is NULL THEN '' ELSE '<' + CAST("Length" AS VARCHAR(10)) + '>') + IF("Precision" IS NULL THEN '' ELSE '<'+CAST("Precision" AS VARCHAR(10)) + IF("Scale" IS NULL THEN ', 0>' ELSE + ', ' + CAST("Scale" AS VARCHAR(10))+ '>')) +')' + IF(Nullable = true THEN ' [NN]' ELSE '') + IF(GenerateExpr IS NULL then '' ELSE ' [Generated]') + IF(ComputeExpr IS NULL THEN '' ELSE ' [Computed]') + IF(DefaultExpr IS NULL THEN '' ELSE ' DEFAULT ' + CAST(DefaultExpr AS VARCHAR(40)) ) as FieldDescription FROM Information.TableColumns WHERE TableName = 'Production' ORDER BY TableName ---- You can then write SQL in the form: SELECT FieldDescription FROM TableColumnSummary WHERE TableName = 'Products' and get a nicely formatted list of fields. |
Tue, Dec 2 2014 7:43 PM | Permanent Link |
David Cornelius Cornelius Concepts | I would personally separate out OrdinalPos to its own field, but it's easy
to modify for one's own purpose and this does look useful. Thanks for sharing! -- David Cornelius Cornelius Concepts |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |