Solved

T-SQL Columns

Posted on 2013-05-15
7
227 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
  • 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 7 31
query question 12 32
MS SQL Server select from Sub Table 14 23
RESTORE MASTER DATABASE -- NOW 2 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 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