enumerate tables, columns using T-SQL ???

enumerate tables, columns using T-SQL ???

Post by Social Fund of Cambodi » Thu, 11 May 2000 04:00:00



Could anyone advice how to enumerate tables and then enumerate columns (of
that table) using T-SQL

Thank you

SAM

 
 
 

enumerate tables, columns using T-SQL ???

Post by Dan Guzma » Thu, 11 May 2000 04:00:00


There are a couple of different methods to list tables and columns.  You can
execute sp_help to list all database objects and then execute sp_help
'TableName' to list table details.  If you are Using SQL 7, you can also use
the INFORMATION_SCHEMA views:

Select t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME
From INFORMATION_SCHEMA.TABLES t
Where t.TABLE_TYPE = 'BASE TABLE'

Select t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME
From INFORMATION_SCHEMA.TABLES t
Join INFORMATION_SCHEMA.COLUMNS c On
 t.TABLE_CATALOG = c.TABLE_CATALOG And
 t.TABLE_SCHEMA = c.TABLE_SCHEMA And
 t.TABLE_NAME = c.TABLE_NAME And
 t.TABLE_TYPE = 'BASE TABLE'

Hope this helps.



Quote:> Could anyone advice how to enumerate tables and then enumerate columns (of
> that table) using T-SQL

> Thank you

> SAM


 
 
 

enumerate tables, columns using T-SQL ???

Post by Dan Guzma » Thu, 11 May 2000 04:00:00


There are a couple of different methods to list tables and columns.  You can
execute sp_help to list all database objects and then execute sp_help
'TableName' to list table details.  If you are Using SQL 7, you can also use
the INFORMATION_SCHEMA views:

Select t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME
From INFORMATION_SCHEMA.TABLES t
Where t.TABLE_TYPE = 'BASE TABLE'

Select t.TABLE_CATALOG, t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME
From INFORMATION_SCHEMA.TABLES t
Join INFORMATION_SCHEMA.COLUMNS c On
 t.TABLE_CATALOG = c.TABLE_CATALOG And
 t.TABLE_SCHEMA = c.TABLE_SCHEMA And
 t.TABLE_NAME = c.TABLE_NAME And
 t.TABLE_TYPE = 'BASE TABLE'

Hope this helps.



Quote:> Could anyone advice how to enumerate tables and then enumerate columns (of
> that table) using T-SQL

> Thank you

> SAM

 
 
 

enumerate tables, columns using T-SQL ???

Post by Dana Da » Thu, 11 May 2000 04:00:00


You can also use sp_tables or sp_columns.
Dana Day
Sr. DBA


Quote:> Could anyone advice how to enumerate tables and then enumerate columns (of
> that table) using T-SQL

> Thank you

> SAM

 
 
 

enumerate tables, columns using T-SQL ???

Post by Social Fund of Cambodi » Fri, 12 May 2000 04:00:00


Dear Uwe Ricken,

Thank very much for your advice. I was thinking of enumarating through
database tables and pass the table names to a variable. Because I am new to
SQL, could you advice on how to programmatically perform the following
'ALTER TABLE ....' below:

ALTER TABLE stdComponent DROP COLUMN On_Fund
ALTER TABLE stdComponentActivitiy DROP COLUMN On_Fund
ALTER TABLE stdStatusChange DROP COLUMN On_Fund
ALTER TABLE sysEmployees DROP COLUMN On_Fund
ALTER TABLE sysRegionalPrices DROP COLUMN On_Fund
ALTER TABLE sysReport DROP COLUMN On_Fund
ALTER TABLE sysTransport DROP COLUMN On_Fund
ALTER TABLE tblActivities DROP COLUMN On_Fund
ALTER TABLE tblCommunes DROP COLUMN On_Fund
ALTER TABLE tblComponent DROP COLUMN On_Fund
...
...
...

I have about 50 tables and it will require me to write 50 lines. I know
there is a way to replace table name with a variable while looping through
them but I don't know how to do it.

Thank a lot

SAM

> Hi Sam,

> check the tables sysObjects and sysColumns

> e.g.

> SELECT o.Name, c.Name
> FROM sysObjects o INNER JOIN sysColumns c
> ON (o.id = c.id)

> Best regards,

> Uwe Ricken
> MCP FOR MS ACCESS DEVELOPMENT
> GNS GmbH, Frankfurt am Main

> Social Fund of Cambodia schrieb in Nachricht

> >Could anyone advice how to enumerate tables and then enumerate columns
(of
> >that table) using T-SQL

> >Thank you

> >SAM

 
 
 

enumerate tables, columns using T-SQL ???

Post by William Talad » Fri, 12 May 2000 04:00:00


-- just execute and paste the results of one line
SELECT 'ALTER TABLE '+TABLE_NAME+' DROP COLUMN id'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id'


>Dear Uwe Ricken,

>Thank very much for your advice. I was thinking of enumarating through
>database tables and pass the table names to a variable. Because I am new to
>SQL, could you advice on how to programmatically perform the following
>'ALTER TABLE ....' below:

>ALTER TABLE stdComponent DROP COLUMN On_Fund
>ALTER TABLE stdComponentActivitiy DROP COLUMN On_Fund
>ALTER TABLE stdStatusChange DROP COLUMN On_Fund
>ALTER TABLE sysEmployees DROP COLUMN On_Fund
>ALTER TABLE sysRegionalPrices DROP COLUMN On_Fund
>ALTER TABLE sysReport DROP COLUMN On_Fund
>ALTER TABLE sysTransport DROP COLUMN On_Fund
>ALTER TABLE tblActivities DROP COLUMN On_Fund
>ALTER TABLE tblCommunes DROP COLUMN On_Fund
>ALTER TABLE tblComponent DROP COLUMN On_Fund
>...
>...
>...

>I have about 50 tables and it will require me to write 50 lines. I know
>there is a way to replace table name with a variable while looping through
>them but I don't know how to do it.

>Thank a lot

>SAM



 
 
 

enumerate tables, columns using T-SQL ???

Post by Social Fund of Cambodi » Sat, 13 May 2000 04:00:00


Dear Willian Talada,

After running this statement, I found that the specified column is not
deleting from the table.

Do you have anymore suggestion??
SAM


> -- just execute and paste the results of one line
> SELECT 'ALTER TABLE '+TABLE_NAME+' DROP COLUMN id'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE COLUMN_NAME = 'id'




> >Dear Uwe Ricken,

> >Thank very much for your advice. I was thinking of enumarating through
> >database tables and pass the table names to a variable. Because I am new
to
> >SQL, could you advice on how to programmatically perform the following
> >'ALTER TABLE ....' below:

> >ALTER TABLE stdComponent DROP COLUMN On_Fund
> >ALTER TABLE stdComponentActivitiy DROP COLUMN On_Fund
> >ALTER TABLE stdStatusChange DROP COLUMN On_Fund
> >ALTER TABLE sysEmployees DROP COLUMN On_Fund
> >ALTER TABLE sysRegionalPrices DROP COLUMN On_Fund
> >ALTER TABLE sysReport DROP COLUMN On_Fund
> >ALTER TABLE sysTransport DROP COLUMN On_Fund
> >ALTER TABLE tblActivities DROP COLUMN On_Fund
> >ALTER TABLE tblCommunes DROP COLUMN On_Fund
> >ALTER TABLE tblComponent DROP COLUMN On_Fund
> >...
> >...
> >...

> >I have about 50 tables and it will require me to write 50 lines. I know
> >there is a way to replace table name with a variable while looping
through
> >them but I don't know how to do it.

> >Thank a lot

> >SAM



 
 
 

enumerate tables, columns using T-SQL ???

Post by Tibor Karasz » Sat, 13 May 2000 04:00:00


Williams suggestion was that after running the statement, you use the result
from it and paste it into the query pane. Then you execute.

I.e., Williams suggestion generates the DROP statements for you, but you
have to execute them.

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.



> Dear Willian Talada,

> After running this statement, I found that the specified column is not
> deleting from the table.

> Do you have anymore suggestion??
> SAM



> > -- just execute and paste the results of one line
> > SELECT 'ALTER TABLE '+TABLE_NAME+' DROP COLUMN id'
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE COLUMN_NAME = 'id'