Solved

T-SQL Columns

Posted on 2013-05-15
7
228 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 65

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 65

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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