How to list my SQL table column/field names?

Well yes.

The view is ...

SELECT     db_name() AS TABLE_CATALOG, user_name(obj.uid) AS TABLE_SCHEMA, obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,
                      col.colid AS ORDINAL_POSITION, com.text AS COLUMN_DEFAULT, CASE col.isnullable WHEN 1 THEN 'YES' ELSE 'No ' END AS IS_NULLABLE,
                      spt_dtp.LOCAL_TYPE_NAME AS DATA_TYPE, CONVERT(int, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin)
                      AS CHARACTER_MAXIMUM_LENGTH, CONVERT(int, spt_dtp.charbin + CASE WHEN spt_dtp.LOCAL_TYPE_NAME IN ('nchar', 'nvarchar', 'ntext')
                      THEN 2 * OdbcPrec(col.xtype, col.length, col.xprec) ELSE OdbcPrec(col.xtype, col.length, col.xprec) END) AS CHARACTER_OCTET_LENGTH,
                      NULLIF (col.xprec, 0) AS NUMERIC_PRECISION, spt_dtp.RADIX AS NUMERIC_PRECISION_RADIX, col.scale AS NUMERIC_SCALE,
                      spt_dtp.SQL_DATETIME_SUB AS DATETIME_PRECISION, CONVERT(sysname, NULL) AS CHARACTER_SET_CATALOG, CONVERT(sysname, NULL)
                      AS CHARACTER_SET_SCHEMA, CONVERT(sysname, CASE WHEN spt_dtp.LOCAL_TYPE_NAME IN ('char', 'varchar', 'text')
                      THEN a_cha.name WHEN spt_dtp.LOCAL_TYPE_NAME IN ('nchar', 'nvarchar', 'ntext') THEN N'Unicode' ELSE NULL END) AS CHARACTER_SET_NAME,
                      CONVERT(sysname, NULL) AS COLLATION_CATALOG, CONVERT(sysname, NULL) AS COLLATION_SCHEMA, col.collation AS COLLATION_NAME,
                      CONVERT(sysname, CASE WHEN typ.xusertype > 256 THEN DB_NAME() ELSE NULL END) AS DOMAIN_CATALOG, CONVERT(sysname,
                      CASE WHEN typ.xusertype > 256 THEN USER_NAME(obj.uid) ELSE NULL END) AS DOMAIN_SCHEMA, CONVERT(sysname,
                      CASE WHEN typ.xusertype > 256 THEN typ.name ELSE NULL END) AS DOMAIN_NAME
FROM         sysobjects obj, master.dbo.spt_datatype_info spt_dtp, systypes typ, syscolumns col LEFT OUTER JOIN
                      syscomments com ON col.cdefault = com.id AND com.colid = 1, master.dbo.syscharsets a_cha
WHERE     permissions(obj.id, col.name) != 0 AND obj.id = col.id AND typ.xtype = spt_dtp.ss_dtype AND (spt_dtp.ODBCVer IS NULL OR
                      spt_dtp.ODBCVer = 2) AND obj.xtype IN ('U', 'V') AND col.xusertype = typ.xusertype AND (spt_dtp.AUTO_INCREMENT IS NULL OR
                      spt_dtp.AUTO_INCREMENT = 0) AND a_cha.id = isnull(CONVERT(tinyint, CollationPropertyFromID(col.collationid, 'sqlcharset')), CONVERT(tinyint,
                      ServerProperty('sqlcharset')))

Which is a LITTLE bit of a mouthful!

Richard.

Richard,

The view definition is not relevant here.
The information_schema views are provided by SQL Server to make life easier for DBAs and developpers, whithout messing around with the system tables.

information_schema views should be used whenever it's possible, since system tables will change in SQL server 2005.

So either you use the view or not, but using the code inside it kindof defeats the purpose of the view ...

Or did I miss something in your comment >>Which is a LITTLE bit of a mouthful!<<

Regards

Hilaire

https://www.experts-exchange.com/questions/21126055/Index-Details-Of-a-Table-in-SQL-Server.html

Hi,

An easy way to do this....

SELECT     COLUMN_NAME
FROM         INFORMATION_SCHEMA.COLUMNS
WHERE     (TABLE_NAME = 'your table name')

TRY THIS STATEMENT

SELECT C.NAME FROM SYSCOLUMNS C
INNER JOIN SYSOBJECT O
ON C.ID = O.ID WHERE O.NAME ='TABLENAME'


NOTE: USE THIS STATEMENT IN DTS OR IN QUERY ANALYZER TAKE RESULT IN GRID AND COPY PASTE IN EXCEL.

Step 1) Use sp_columns 'tablename' function to list all columns & data types for the given table.
Step 21) You may then export the returned list to a file
      Or
Step 22) Join Sysobjects (so) to SysColumns (sc) table on [id] & select so.name & sc.name order by so.name, sc.name , sc.colorder to get all tables & their columns.

Paste this script into an Excel document macro, edit the DSN to reflect your server,database, and password.
Have Fun!
Ther are two macros. The Columns macro will genrate a list of TABLES and COLUMS.
'-----------------------------
Function DSN()
'Choose one of these formats and set DSN equal to the correct data source
'Need to set: {DSN=|SERVER=};UID=;PWD=;DATABASE=;
'With Pre-Defined ODBC DSN
DSN1 = "ODBC;DSN=Great Plains KK;UID=DYNSA;PWD=0129;DATABASE=TEST;"
'Direct DSN Definition
DSN2 = "ODBC;DRIVER=SQL Server;SERVER=SQLSRV;UID=sa;PWD=0129;DATABASE=TEST"
'Select the one to use
DSN = DSN2
End Function
Sub Tables()
    Q = ""
    Q = Q & " SELECT DISTINCT"
    Q = Q & "  [sysobjects].[name] AS Table_Name"
    'Q = Q & " , [systypes].[name] as Xtype"
    'Q = Q & " , [syscolumns].[xtype]"
    'Q = Q & " , [syscolumns].[xusertype]"
    'Q = Q & " , [syscolumns].[length]"
    'Q = Q & " , [cdefault]"
    'Q = Q & " , [syscolumns].[domain]"
    'Q = Q & " , [syscolumns].[collationid]"
    'Q = Q & " , [syscolumns].[type]"
    'Q = Q & " , [syscolumns].[usertype]"
    'Q = Q & " , [syscolumns].[prec]"
    'Q = Q & " , [syscolumns].[scale]"
    'Q = Q & " , [iscomputed]"
    'Q = Q & " , [isoutparam]"
    'Q = Q & " , [isnullable]"
    Q = Q & " FROM [syscolumns], [sysobjects], [systypes]"
    Q = Q & " where [syscolumns].[ID] = [sysobjects].[ID]"
    Q = Q & " and [sysobjects].[xtype] in ('V', 'U')"
    Q = Q & " and [syscolumns].[xtype]=[systypes].[xtype]"
    'Q = Q & " and [syscolumns].[id]> 100"
    'Q = Q & " order by  ParentObj,[syscolumns].[name]"

   doquery Q, "Tables"
End Sub
Sub Columns()
    Q = ""
    Q = Q & " SELECT [syscolumns].[name]"
    Q = Q & " , [sysobjects].[name] AS ParentObj"
    Q = Q & " , [systypes].[name] as Xtype"
    Q = Q & " , [syscolumns].[xtype]"
    Q = Q & " , [syscolumns].[xusertype]"
    Q = Q & " , [syscolumns].[length]"
    'Q = Q & " , [cdefault]"
    'Q = Q & " , [syscolumns].[domain]"
    'Q = Q & " , [syscolumns].[collationid]"
    Q = Q & " , [syscolumns].[type]"
    Q = Q & " , [syscolumns].[usertype]"
    Q = Q & " , [syscolumns].[prec]"
    Q = Q & " , [syscolumns].[scale]"
    'Q = Q & " , [iscomputed]"
    'Q = Q & " , [isoutparam]"
    'Q = Q & " , [isnullable]"
    Q = Q & " FROM [syscolumns], [sysobjects], [systypes]"
    Q = Q & " where [syscolumns].[ID] = [sysobjects].[ID]"
    Q = Q & " and [sysobjects].[xtype] in ('V', 'U')"
    Q = Q & " and [syscolumns].[xtype]=[systypes].[xtype]"
    'Q = Q & " and [syscolumns].[id]> 100"
    Q = Q & " order by  ParentObj,[syscolumns].[name]"

   doquery Q, "Columns"
End Sub
Sub Objects()
    Q = ""
    Q = Q & " SELECT [A].[name]"
    'Q = Q & ", [A].[id]"
    Q = Q & " , CASE [A].[xtype]"
    Q = Q & "   WHEN 'C'  Then '4 Constraint'"
    Q = Q & "   WHEN 'D'  Then '5 Default'"
    Q = Q & "   WHEN 'F'  Then '6 Foreign'"
    Q = Q & "   WHEN 'L'  Then '9 Log'"
    Q = Q & "   WHEN 'FN' Then '8 Scaler Function'"
    Q = Q & "   WHEN 'IF' Then '9 Inline Table'"
    Q = Q & "   WHEN 'P'  Then '7 Procedure'"
    Q = Q & "   WHEN 'PK' Then '1 Primary Key'"
    Q = Q & "   WHEN 'RF' Then '9 Filter'"
    Q = Q & "   WHEN 'S'  Then '9 System Table'"
    Q = Q & "   WHEN 'TF' Then '8 Table Function'"
    Q = Q & "   WHEN 'TR' Then '7 Trigger'"
    Q = Q & "   WHEN 'U'  Then '0 User Table'"
    Q = Q & "   WHEN 'UQ' Then '2 Unique'"
    Q = Q & "   WHEN 'V'  Then '3 View'"
    Q = Q & "   WHEN 'X'  Then 'Extended Proc'"
    Q = Q & "   Else '9 Unknown'"
    Q = Q & "  END As Xtype"
   'Q = Q & " ,[A].[parent_obj], [B].[name] as Parent, [A].[type]"
    Q = Q & " ,[B].[name] as Parent, [A].[type]"
    Q = Q & " FROM [sysobjects] AS A LEFT JOIN  [sysobjects] AS B"
    Q = Q & vbCrLf
    Q = Q & " ON [A].[parent_obj]=B.[id]"
    'Q = Q & " where [Xtype] in  ('U','V','P')"
    'Q = Q & " WHERE [A].[parent_obj]=B.[id]"
    Q = Q & " order by [A].[Xtype],[A].[name]"

   doquery Q, "Objects"
   
End Sub
Sub Procs()
    Q = ""
    Q = Q & " SELECT [syscolumns].[name]"
    Q = Q & " , [sysobjects].[name] AS Parent"
    Q = Q & " , [systypes].[name] as Xtype"
    Q = Q & " , [syscolumns].[xtype]"
    Q = Q & " , [syscolumns].[xusertype]"
    Q = Q & " , [syscolumns].[length]"
    Q = Q & " , [cdefault]"
    Q = Q & " , [syscolumns].[domain]"
    Q = Q & " , [syscolumns].[collationid]"
    Q = Q & " , [syscolumns].[type]"
    Q = Q & " , [syscolumns].[usertype]"
    Q = Q & " , [syscolumns].[prec]"
    Q = Q & " , [syscolumns].[scale]"
    Q = Q & " , [iscomputed]"
    Q = Q & " , [isoutparam]"
    Q = Q & " , [isnullable]"
    Q = Q & " FROM [syscolumns], [sysobjects], [systypes]"
    Q = Q & " where [syscolumns].[ID] = [sysobjects].[ID]"
    Q = Q & " and [sysobjects].[xtype] in ('P','X','IF','FN')"
    Q = Q & " and [syscolumns].[xtype]=[systypes].[xtype]"
    'Q = Q & " and [syscolumns].[id]> 100"
    Q = Q & " order by Parent,[sysobjects].[xtype]"
   doquery Q, "ProcsPrameters"
End Sub

Sub doquery(Q, N)
    X = ActiveSheet.Cells(1, 1)
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    ActiveSheet.Cells(1, 1) = X
    With ActiveSheet.QueryTables.Add(Connection:=DSN, Destination:=Range("A2"))
        .CommandText = Q
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = N
End Sub