sys.columns X syscolumns

• JoseACJr

  SSC Eights!

  Points: 934

  More actions

  July 12, 2012 at 5:28 am

  #258830

  Hi everybody,

  I will create an application to list the columns of my database, what the difference between sys.columns and syscolumns ?

  They are not tables? which tables these views access ?

  Thanks,

  Jose A. Carvalho Jr

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 12, 2012 at 5:39 am

  #1511986

  JoseACJr (7/12/2012)

  ---

  Hi everybody,

  I will create an application to list the columns of my database, what the difference between sys.columns and syscolumns ?

  They are not tables? which tables these views access ?

  Thanks,

  Jose A. Carvalho Jr

  both are views.

  syscolumns is a view that is deprecated and will be dropped in an upcoming version of SQL; it was left in place for compatibility when SQL 2005 was introduced. In versions 2000 and prior, it was the actual meta data of the columns,a nd you could potentially directly edit that data; in 2005 it became a true view, and the ability to directly manipulate the meta data was removed (from those types of compatibility views)

  sys.columns is a view that MS plans on maintaining; they may add more columns to the end int eh future, but the columns and order of what exists now should stay the same.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 12, 2012 at 9:35 am

  #1512141

  You could also use the ANSI standard INFORMATION_SCHEMA.COLUMNS view. I have used that in an application that had to work with SQL Server, SYBASE, DB2.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question