Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2005, 12:47 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default Query for Column Names and Datatypes

Does anyone know how to query MS-SQL to find the column names and datatypes set for those columns?

I'm looking to get the same information that you look at when you select 'Design Table' in Enterprise Manager.

Thanks for any help on this issue.



 
Old August 23rd, 2005, 01:13 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Seems to me you can query the syscolumns table and get most of this info.
Name is the column name
id is the table id
xtype is the data type
 You should be able to put a view together for each table and use aliases to splay the proper data.

mmcdonal
 
Old August 23rd, 2005, 02:37 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Sweeeet!

Thanks for the hint. I had been in there around and around. Finally found out that the id in syscolumns was the object id so that I could pull all of the columns from there. So I just joined to systypes to get the data type.

SELECT systypes.name AS 'Column Name', syscolumns.name AS 'Data Type'
FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (syscolumns.id = 697769543)

Much appreciated.

Thanks a million.


 
Old August 23rd, 2005, 02:52 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about using INFORMATION_SCHEMA views than to query the system tables directly?

Select COLUMN_NAME = Left(COLUMN_NAME, 20),
    DATA_TYPE = Left(DATA_TYPE, 10),
    CHARACTER_MAXIMUM_LENGTH as CHAR_MAX_LEN,
    NUMERIC_PRECISION AS NUM_PRECISION,
    NUMERIC_SCALE AS NUM_SCALE
From pubs.Information_Schema.Columns
Where Table_Name = 'authors'





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic column names in crystal reports pankaj_daga Crystal Reports 3 January 19th, 2009 02:55 AM
Displaying Table Column/Fields names in a listbox Durkee VB.NET 2002/2003 Basics 4 September 25th, 2007 03:37 PM
Column names! Help! dirtdog22 Access VBA 6 June 1st, 2007 06:37 AM
show just the column names from a MySQL command crmpicco MySQL 5 December 7th, 2006 08:34 AM
how to Retrieve Column Names Using SQL Query saravananedu Oracle 2 September 10th, 2005 01:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.