SELECT A.name,
       C.name AS datatype,
       A.length,
       A.prec,
       A.scale,
       Convert(bit,(A.status & 0x08))
  FROM syscolumns A,
       systypes C
 WHERE A.id = IsNull(Object_Id(:sTable),
          (SELECT id
            FROM sysobjects a, sysusers b
           WHERE (Lower(a.name) = :sTable
              OR Lower(b.name) + '.' + Lower(a.name) = :sTable)
             AND a.uid = b.uid
           GROUP BY a.name
           HAVING Min(a.uid) = a.uid))
   AND A.usertype = C.usertype
 ORDER BY A.colid

SELECT COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       DATA_SCALE,
       CASE NULLABLE WHEN 'Y' THEN 1 ELSE 0 END,
       COLUMN_ID
  FROM ALL_TAB_COLS
 WHERE TABLE_NAME = :sTable
   AND OWNER = :sOwner

SELECT A.name,
       C.name AS datatype,
       A.length,
       A.prec,
       A.scale,
       CONVERT(bit,(A.status & 0x08))
  FROM syscolumns A,
       sysobjects B,
       systypes C
 WHERE A.id = B.id
   AND A.xusertype = C.xusertype
   AND B.id = IsNull(Object_Id(:sTable),
          (SELECT TOP 1 a.id
            FROM sysobjects a, sysusers b
           WHERE (Lower(a.name) = Lower(:sTable)
              OR Lower(b.name) + '.' + Lower(a.name) = Lower(:sTable))
             AND a.uid = b.uid))
 ORDER BY A.colorder

SELECT c.column_name,
       d.domain_name,
       c.width,
       c.width,
       c.scale,
       CASE c.nulls WHEN 'Y' THEN 1 ELSE 0 END AS nulls
  FROM sys.syscolumn c
       JOIN sys.sysdomain d ON c.domain_id=d.domain_id
       JOIN sys.systable t ON c.table_id=t.table_id
  WHERE t.table_id = (SELECT A.table_id
                        FROM sys.SYSTABLE A, dbo.sysusers B
                       WHERE (upper(B.Name) + '.' +
                             upper(A.table_name) = upper(:sTable) OR
                             upper(A.table_name) = upper(:sTable))
                         AND A.creator = B.suid)
 ORDER BY C.column_id

QweryBuilder Trial 19,672 KB - Version: 7.1.1

QweryBuilder is an SQL Editor and Query Tool. Its purpose is to make accessing information from a database simple, quick, accurate and efficient. Work with your SQL Server, ASE, SQL Anywhere and Oracle databases in one single application.