Solved

T-SQL Columns

Posted on 2013-05-15
7
232 Views
Last Modified: 2013-05-15
In T-SQL (SQL Server 2000).   How can I list all tables and columns in a database?
Also, in a separate query is there a way to list all columns along with data type and constraints (NULLS, etc).   Thanks.
0
Comment
Question by:fjkaykr11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39169264
Give this a whirl...

SELECT t.name, c.name
FROM sys.tables t
      JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.type_desc='USER_TABLE'
ORDER BY t.name, c.name

You can explore 'SELECT * FROM sys.columns' to include other column properties.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39169278
getting error valid object name 'sys.tables'.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39169309
Sorry, SQL 2000, didn't notice that.   I gave you the SQL 2012 answer.

I don't have SQL 2000 on my box, so I'll withdraw from the question to encourage other experts to respond.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Author Comment

by:fjkaykr11
ID: 39169437
I found this on another site, this bring back Table, Column and Datatype. But it doesn't list
which User Database, the table and the columns are in (query below). Any ideas on how to add i the database info?  

select *
from information_schema.columns
order by table_name, ordinal_position
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39169479
>But it doesn't list which User Database,
the database name will be the current one where you run the query

select DB_NAME() as Database_name, * from information_schema.columns
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39169515
That worked.  Thanks so much for the help.
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 39169516
thanks
0

Featured Post

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question