|
Subject:
|
Using System Tables, Views and Stored Procedures
|
|
Posted By:
|
syphon
|
Post Date:
|
7/4/2006 4:07:32 PM
|
Hey, new to the forms here.
Can anyone give me some good articles or links on using the system tables in SQLExpress? Specificly I'm looking for information reguarding finding how many databases exist along with what tables, stored procedures, triggers, views etc exist within those tables and their column names, types and sizes.
Basicly I need to find out what the column type and size is by using the system tables, views, stored procedures, etc.
I've been experimenting with sys.sysdatabases and that can give me a list of the current databases, but I need to go a level deeper.
Curiously enough I've made 3 test databases. When I select the master database and execute "SELECT * FROM sys.tables"; only the tables from the first database appear and the tables from my other two do not appear in the list. However they'll show once run my query on the appropriate database ("USE Test2"). Any comment on this?
So far I could use "sys.sysdatabases" and filter the system databases to get a list of the user databases, and then use
USE [myDataBase]
GO
SELECT * FROM sys.tables
to get all the tables in that database, but what about selecting the column metadata information? Name, type, size, identity (if any) etc?
Thanks in advance!
|
|
Reply By:
|
swan2006
|
Reply Date:
|
9/4/2006 2:18:27 PM
|
hai,
use
SELECT * FROM INFORMATION_SCHEMA.TABLES
|
|
Reply By:
|
harsh_athalye
|
Reply Date:
|
9/6/2006 3:09:35 AM
|
use Information_Schema.Columns to get the column metadata.
Select * from information_schema.columns where table_name = 'sometable'
Harsh Athalye
India
"Nothing is impossible"
|
|
