Listing all fields in a table

[encore0000]

I am fairly new at this I need to write a script which lists all the fields in a table. How can I do this.
thanks

 

[amorous]

The below select query selects all the fields from your table named TableName

SELECT * FROM TableName

Then you can create a table to list them.

VJ

 

[encore0000]

Isnt there a batch script which does that..thanks

 

[mrdenny]

select * 
from information_schema.columns 
where table_name = 'yourtable'

Or you can go directly to the syscolumns and sysobjects tables to get the columns for you table. The informatino_schema views are preferred, as the sysobjects and syscolumns tables can change between versions.

See INFORMATION_SCHEMA.Columns in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[encore0000]

i was thinking something like this

select * MSSQL_Database
Where SQLServerName='MyDB'

thanks

 

[mrdenny]

you want a list of all the databases? I thought you wanted a list of the columns in a table?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[encore0000]

what does INFORMATION_SCHEMA.Columns means?? while we are at this how do i display all the tables, the fields ...
thansk

 

[encore0000]

i mean i did want for one table but i was just wondering if i got do it for the entire database. which list all teh tbls and under each tbl all the fields.. thanks bro

 

[mrdenny]

INFORMATION_SCHEMA.COLUMNS is a system view that microsoft created so that you could view all the columns in all the tables. There are about a dozzen of these views. Check in Books On Line (BOL), it will tell you what they are, and what all the information in them means. You can find BOL in the Micorosft SQL Server program group, off of your start button.

To get the list of tables use:

select *
from INFORMATION_SCHEMA.TABLES

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mrdenny]

If you use the INFORMATION_SCHEMA.COLUMNS view it will show you all the tables and all their columns. Simply do an order by TABLE_NAME.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[encore0000]

How do I write and run this as a script if I wanted to.. meaning how do i execute it Thanks alot bro

 

[mrdenny]

use your_database
go
select *
from INFORAMTION_SCHEMA.COLUMNS
order by TABLE_NAME

Run that in Query Analyser.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[encore0000]

thanks a bunch let me try that..

 

[encore0000]

i am running it on the server how do i output it
like use
response.write() or something like that

 

[encore0000]

how do i display the results I dont have a Query Analyer.. i have an asp file looking like the following

<%
Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.open("Provider=SQLOB; Data Source=GHOST; Initial Catalog=hugees; User ID=hugees; Password=testi")
go
select *
from INFORAMTION_SCHEMA.COLUMNS
order by TABLE_NAME
%>

 

[mrdenny]

If you just need to get data out of the server,have your DBA install the SQL tools on your machine. That will give you query analyser and Enterprise Manager. For ASP code you'll want to check in the ASP forums.

Denny

--Anything is possible. All it takes is a little research. (Me)