Your database<\/a> doesn’t just contain your data.<\/p>\n\n\n\n
It also contains data about your data in System Tables.<\/p>\n\n\n\n
In SQL Server<\/a> these are often referred to as System Tables and views. They can be found in the master database, which holds data about the database. And in the system views within each database for specific information about each database<\/a>.<\/p>\n\n\n\n
sys.objects<\/strong> – shows each object, type and created date<\/p>\n\n\n\n
sys.indexes<\/strong> – shows each index and type<\/p>\n\n\n\n
information_schema.columns<\/strong> – shows each column, it’s position and datatype<\/p>\n\n\n\n
In PostgreSQL, a similar collection of tables can be found in the information_schema and PostgreSQL catalogue.<\/p>\n\n\n\n
information_schema.tables<\/strong> – each object, type and created date<\/p>\n\n\n\n
pg_index<\/strong> – shows each index and type<\/p>\n\n\n\n
information_schema.columns<\/strong> – shows each column, it’s position and datatype<\/p>\n\n\n\n
To illustrate how useful these can be, and which views and tables you need, here are six scripts in SQL Server and PostgreSQL.<\/p>\n\n\n\n
Count columns<\/a>
Count rows<\/a>
Show data types<\/a>
Search for a column name<\/a>
Show all tables in a schema<\/a>
Show number of tables in each schema<\/a><\/strong><\/p><\/blockquote>\n\n\n\nTested using SQL Server Standard<\/a> and Aurora (Postgres)<\/a><\/em><\/p><\/blockquote>\n\n\n\n
\n\n\n\nCount Columns<\/h2>\n\n\n\n
This query returns a list of tables, in alphabetical order, with a count of the columns. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nselect\n table_schema, \n table_name, \n count(*) as column_count\nfrom information_schema.columns\nwhere table_catalog = 'mydatabase' -- put your DB here\ngroup by \n table_schema, \n table_name\norder by \n table_schema, \n table_name<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect \n table_name, \n count(column_name)\nfrom \n information_schema.columns\nwhere \n table_schema = 'myschema' -- put your schema here\ngroup by \n table_name\norder by \n table_name;<\/pre>\n\n\n\n
\n\n\n\nCount Rows<\/h2>\n\n\n\n
This query returns a list of tables, in alphabetical order, with a count of the rows. In the case of SQL Server, this column will contain the schema and table name. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nuse mydatabase -- put your DB here\ngo\n\nselect\nquotename(schema_name(sobj.schema_id)) \n+ '.' + quotename(sobj.name) as table_name,\nsum(sptn.rows) as row_count\nfrom\n sys.objects as sobj\ninner join \n sys.partitions as sptn on sobj.object_id = sptn.object_id\nwhere\n sobj.type = 'U'\n and sobj.is_ms_shipped = 0x0\n and index_id < 2 -- 0:Heap, 1:Clustered\ngroup by \n sobj.schema_id, \n sobj.name\norder by \n table_name<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect\n schemaname,\n relname,\n n_live_tup\nfrom \n pg_stat_user_tables\nwhere \n schemaname = 'myschema'\norder by \n relname;<\/pre>\n\n\n\n
\n\n\n\nShow data types<\/h2>\n\n\n\n
This query returns a list of tables, in alphabetical order, with their column names, data types and lengths. In the case of SQL Server, this also has a column for the schema name. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nuse mydatabase -- put your DB here\ngo\n\nselect \n c.table_schema, \n c.table_name, \n c.column_name, \n c.data_type, \n c.character_maximum_length\nfrom \n information_schema.columns c\njoin sys.objects o on c.table_name = o.name\nwhere \n type = 'u' -- u is for user created tables\norder by \n c.table_schema<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect \n column_name, \n data_type \nfrom \n information_schema.columns \nwhere \n table_schema = 'myschema';\n<\/pre>\n\n\n\n
\n\n\n\nSearch for a column name<\/h2>\n\n\n\n
This query returns a list of column names that match the search criteria in the WHERE clause. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nuse mydatabase -- put your DB here\ngo\n\nselect\n scol.name as column_name,\n tab.name as table_name\nfrom\n sys.columns scol\njoin sys.tables tab on scol.object_id = tab.object_id\nwhere \n scol.name = 'mycolumn' --put your column name here<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect \n column_name,\n table_name\nfrom \n information_schema.columns \nwhere \n column_name = 'mycolumn' --put your column name here\n and table_schema = 'myschema' -- put your schema here<\/pre>\n\n\n\n
\n\n\n\nShow all tables in a schema<\/h2>\n\n\n\n
This query returns a list of tables, in alphabetical order, from the schema or database requested. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nuse mydatabase -- put your DB here\ngo\n\nselect \n [table] = s.name + N'.' + t.name\nfrom \n sys.tables AS t\ninner join sys.schemas as s on t.schema_id = s.schema_id\norder by \n [table]<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect\n table_name \nfrom \n information_schema.tables \nwhere \n table_schema = 'myschema' -- put your schema here\norder by \n table_name<\/pre>\n\n\n\n
\n\n\n\nShow number of tables in each schema<\/h2>\n\n\n\n
This query returns a list of tables, in alphabetical order, with their last modified and created date. Add your schema or database name to the code and run the query.<\/p>\n\n\n\n
\n\n\n\n-- SQL Server\n\nuse mydatabase -- put your DB here\ngo\n\nselect\n schema_name(schema_id) as schema_name,\n count(name) as table_count\nfrom \n sys.tables\ngroup by \n schema_name(schema_id)\norder by \n schema_name(schema_id)<\/pre>\n\n\n\n
\n\n\n\n-- PostgreSQL\n\nselect \n schemaname, \n count(tablename)\nfrom \n pg_tables \ngroup by \n schemaname\norder by \n schemaname<\/pre>\n\n\n\n
\n\n\n\nPhoto by Jenna Hamra<\/a><\/strong> from Pexels<\/a><\/strong> <\/p>\n","protected":false},"excerpt":{"rendered":"