Exploring SQL databases using the dataset builder

Randall Berg -

Microsoft SQL Here are a few queries that you can use to find data within a Microsoft SQL database:

  • find all tables

    Select top 10000 * from Information_Schema.Tables
    Where table_type = 'BASE TABLE'
    order by Table_Name asc
  • find all views

    Select top 100000 * from Information_Schema.Tables
    Where table_type = 'VIEW'
    order by table_name asc
  • find tables or views with a specific string in the name

    Select top 10000 * from Information_Schema.Tables
    Where table_name like '%string_here%'
    order by Table_Name asc
  • find all columns with a specific string (tables only)

    SELECT top 100000000 t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name as column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE '%column_name_here%'
    ORDER BY schema_name, table_name

mySQL, postgreSQL, and mariaDB

Here it's important to have the right schema as they have lots of tables that will not be relevant to you. An easy way to find the right schema is to use the database name from your datasource setup page.

  • find all tables in a specific schema.

    Select TABLE_NAME
    FROM information_schema.TABLES
    where table_schema = 'your_schema_here'
    and TABLE_TYPE = 'BASE TABLE'
  • find all views in a specific schema.

    Select TABLE_NAME
    FROM information_schema.TABLES
    where table_schema = 'your_schema_here'
    and TABLE_TYPE = 'VIEW'
  • find all columns with a specific string (tables only)

    SELECT TABLE_NAME,
    COLUMN_NAME
    FROM
    INFORMATION_SCHEMA.COLUMNS
    WHERE column_name LIKE '%column_name_here%'
  • find all columns names and types for a specific table

    SELECT *
    FROM
    INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table_name_here'
Have more questions? Submit a request

Comments

Powered by Zendesk