Dynamic Clause SELECT

Database
Enthusiast

Dynamic Clause SELECT

Hi ALL,
I have to calculate for specific schema and for each table in this schema how many columns and how many records it has, I succeed to calculate the number of columns using the following request:

SELECT TableName, COUNT(ColumnName) "
FROM DBC.ColumnsX
WHERE DatabaseName = 'DB_NAME'
GROUP BY (TableName)
ORDER BY (TableName)

What I want is to have in the final output three columns:
1. TableName
2. Number of Columns
3. number of Records

I bet that I have to use either JOIN statement or improve my record in order to be dynamic one so I can get the output I need, please help me to find out a solution for my issue.

Thank you by advanced,
3 REPLIES
Enthusiast

Re: Dynamic Clause SELECT

Can you write a Stored Procedure? If so, you can take the DatabaseName in as a parameter and iterate through all of the tables using a cursor. At each step, you do a count(*) and store the data in a temporary table with the database and table names. Then, just join to your above query and return a result set.
Enthusiast

Re: Dynamic Clause SELECT

Good idea!Thank you mnylin, I thought to use PL-SQL, but I tried a simple example, however Teradata SQL assistant rejected it, it seems that I have to use a specific syntax, would you please give me an example, I'll check in the internet as well....
Enthusiast

Re: Dynamic Clause SELECT

finally I found an example on the following website:
http://readvitamin.com/2007/08/20/how-to-create-a-stored-procedure-in-teradata/