T-SQL

Question

Post reply

T-SQL

b_boy

SSCertifiable

Points: 5145
More actions
November 23, 2007 at 5:26 am

#377177

Hello I have a huge database and i need to know how i can list the tables within a database, so to know what and what tables are existing.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT * FROM sysobjects WHERE type = 'U'

--Ramesh

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

To add to that... sp_help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Try to avoid select * at any point of time.

Do write your sqlcode like

------------------

Select id,

name,

type,

crdate

From sysobjects

Where type = 'U'

-------------------

instead of

---------------

Select *

From sysobjects

Where type = 'U'

----------------

You can include whatever you wish to view in the output, just for example, i took id,name,type,crdate in the select list.

It is a good practice to specify the table columns explicitly.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Jeff,By mistakenly i took your word 'Running Total'.

Reason 1:

SELECT * queries are also bad from an application maintenance point of view. If a column is added to a table, the results returned to your application will change in structure. Well programmed applications should be referring to columns by name and shouldn't be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.

Reaon 2:

To reduce the size of worktables select only needed columns from the tables. Avoid using select * unless you need all columns of the tables. This reduces the load on tempdb and the cost of sorting the result tables.

Am i correct ? If i am wrong kindly let me know or if you add some more reasons then it would be truly appreciated.

karthik

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 5

That's better, Karthik...

I would also add a couple of things to that...

Reason 3:

SELECT * will normally cause a table scan (even if it looks like an index scan on the execution plan) which defeats most, if not all, performance benefits of indexing.

Reason 4:

Think of the "pipe". Returning more data to the client than is necessary causes extra and unneeded network traffic.

Whenever someone explains to someone that SELECT * isn't a "best practice", they should also either explain why or provide a URL that explains why. That causes learning on the part of the person you are "talking" with. It helps prevent SQL "clones" that know some of the rules but not the reason for the rules. I, and many others, call it the "Five Monkey's Syndrome"... see the following URLs for that...

http://doh-san.blogspot.com/2005/10/five-monkeys.html

http://www.mwls.co.uk/anecdotes/5monkeys.htm

Even the U.S. Navy seems to embrace the lesson taught by the story of the Five Monkeys...

http://www.safetycenter.navy.mil/Articles/a-m/monkeys.htm

Which, of course, leads to...

http://www.urbandictionary.com/define.php?term=five+monkeys

The myth has it, that such an experiment actually took place, although I don't have the details on that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 6

Jeff Moden (11/23/2007)
To add to that... sp_help.

Some more methods

1 EXEC sp_tables

2 In QA, dont point any content and press Alt+F1

Madhivanan

Failing to plan is Planning to fail