CODEWITHSUNDEEP
sqldatabaseteradata
Aditya
Aditya

Reputation: 2311

Teradata - How to add-list all available databases on Teradata server under SQL Assistant Query Tool?

I am using Teradata SQL Assistant. I am a new user to Teradata database. Like in SQL Server Management Studio, I am not able to see the available databases on Teradata Server via SQL Assistant.

So I just wanted to ask, how can I list all the databases available on Teradata Server in SQL Assitant. With this information only, I would be able to decide which databases I need & can ask for the required accesses.

I searched on Google & found we need to manually add databases in SQL Assitant which is not very helpful unless you know the database name. Are there any other tools available which will show me the available databases list in structured-ordered ways like in SSMS ?

Any information-links would be helpful.

Thank you.

Upvotes: 4

Views: 10166

Answers (1)

JNevill
JNevill

Reputation: 50064

As @dnoeth mentioned, you can get a list of databases by querying the DBC.databasesV table. If you want to also see the hierarchy, you can see the OwnerName in that table and create the hierarchy from that parent/child relationship.

A way to show that hierarchy visually in SQL is using a Recursive CTE. In your case:

WITH RECURSIVE dbs AS
(
    SELECT 

        databasename,
        ownername,
        0 AS depth,
        cast(databasename AS VARCHAR(500)) AS path,
        CAST(DatabaseName AS VARCHAR(500)) AS pretty_hierarchy

    FROM "DBC".databasesv
    WHERE DatabaseName = 'DBC'

    UNION ALL

    SELECT
        dbsv.databasename,
        dbsv.ownername,
        depth + 1 AS depth,
        path || '>' || dbsv.databasename,
        substring('            ' FROM 1 FOR (dbs.depth + 1)*3) || dbsv.DatabaseName 
    FROM
        dbs INNER JOIN "DBC".DatabasesV dbsv ON
            dbsv.OwnerName = dbs.databasename AND
            dbsv.DatabaseName <> dbs.databasename
    WHERE dbs.depth <=10
)
SELECT * FROM dbs ORDER BY "path", depth

Upvotes: 4

Related Questions