SQL Script

• anwarul.haque-762710

  Newbie

  Points: 5

  More actions

  November 24, 2008 at 12:06 am

  #196801

  Hi All,

  I need to use sql script.

  How to get a table fieldname, field type, field size, and maximum width of the values in the field that currently stored. And same time store in temp table of returned out put of the SQL script.

  I have a Query that returning tables fields details.

  -------------------

  SELECT obj.name Table_Name, sycol.name Column_Name,Len(sycol.name) 'Data Length', t.name DataType, sycol.Length

  FROM sysobjects obj

  INNER JOIN syscolumns sycol ON (obj.id=sycol.id and obj.type='U' and obj.name not like 'dt%')

  Inner Join systypes t ON (sycol.xtype=t.xtype)

  order by obj.name, sycol.name

  -----------------------------------

  have an idea any body

  Thanks in Advance

  Haque A

• Jeff Moden

  SSC Guru

  Points: 1004666

  More actions

  November 24, 2008 at 12:16 am

  #902293

  If that SELECT is returning the values you want, then...

  SELECT obj.name Table_Name, sycol.name Column_Name,Len(sycol.name) 'Data Length', t.name DataType, sycol.Length

  INTO #yourtemptable

  FROM sysobjects obj

  INNER JOIN syscolumns sycol ON (obj.id=sycol.id and obj.type='U' and obj.name not like 'dt%')

  Inner Join systypes t ON (sycol.xtype=t.xtype)

  order by obj.name, sycol.name

  SELECT collist FROM #yourtemptable

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)