Transact-SQL: ottenere nome, tipo e lunghezza delle colonne di una tabella

CREATE TABLE test (id INT, c1 VARCHAR(10), c2 money, c3 datetime)
 
/* SQL SERVER 2000 */
SELECT syscolumns.name, systypes.name, syscolumns.LENGTH
  FROM syscolumns JOIN systypes ON syscolumns.xusertype = systypes.xusertype
 WHERE id = (SELECT id FROM sysobjects WHERE name = 'test')
 
/* SQL SERVER 2005 2008 */
SELECT sys.COLUMNS.name, sys.types.name, sys.COLUMNS.max_length
  FROM sys.COLUMNS JOIN sys.types
       ON sys.COLUMNS.user_type_id = sys.types.user_type_id
 WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'test')

Risultato:


id int      4
c1 varchar 10
c2 money    8
c3 datetime 8

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>