SQL query to fetch all the tables and column names from the database

Below query is very usefull when you want to search for a particular cloumn name.

SELECT table_name, column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_name in (SELECT name FROM sysobjects WHERE xtype='U')
ORDER BY table_name

Here xtype='U' in the query means user table.

Some of the other options we can use for "xtype" are,
C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure



Author: Asheej T K22 Nov 2009 Member Level: Gold   Points : 1

I didn't know that i have to use code block. I hope this formatting is OK

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: