SELECT n.nspname AS schema_name , pg_get_userbyid(c.relowner) AS table_owner , c.relname AS table_name , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END AS table_type , d.description AS table_description FROM pg_class As c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace LEFT JOIN pg_description As d ON (d.objoid = c.oid AND d.objsubid = 0) WHERE c.relkind IN('r', 'v') ORDER BY n.nspname, c.relname ;
SELECT TRIM(nspname) AS schema_name, TRIM(relname) AS table_name, relcreationtime AS creation_time FROM pg_class_info LEFT JOIN pg_namespace ON pg_class_info.relnamespace = pg_namespace.oid WHERE reltype != 0 AND TRIM(nspname) = 'schemanamehere' and TRIM(relname) = 'tablenamehere';
/*Find long running queries on redshift*/ SELECT pid, trim(user_name), starttime, substring(query,1,20), duration, db_name, status,query FROM stv_recents WHERE status='Running' order by duration desc;
Exports SQL Server ALL database restore scripts, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases, system triggers and backup devices from one SQL Server to another.
Export-DbaInstance consolidates most of the export scripts in dbatools into one command.
This is useful when you’re looking to Export entire instances. It less flexible than using the underlying functions. Think of it as an easy button. Unless an -Exclude is specified, it exports:
All database ‘restore from backup’ scripts. Note: if a database does not have a backup the ‘restore from backup’ script won’t be generated. All logins. All database mail objects. All credentials. All objects within the Job Server (SQL Agent). All linked servers. All groups and servers within Central Management Server. All SQL Server configuration objects (everything in sp_configure). All user objects in system databases. All system triggers. All system backup devices. All Audits. All Endpoints. All Extended Events. All Policy Management objects. All Resource Governor objects. All Server Audit Specifications. All Custom Errors (User Defined Messages). All Server Roles. All Availability Groups. All OLEDB Providers.
The exported files are written to a folder with a naming convention of “machinename$instance-yyyyMMddHHmmss”.
Export-DbaInstance -SqlInstance SQLSERVER01 -Path \\SQLSERVER01\Backups\ -NoPrefix
--Script to generate statements to re-create users in redshift select 'create user '+ usename +' password ''Hidden1!'' ;' from pg_user order by usename asc; --Script to generate statements to re-create groups select 'create group '+ groname +';' from pg_group order by groname asc; --Script to generate statements to add users to various groups SELECT 'alter group '+ groname + ' add user ' + usename + ';' FROM "admin".v_get_users_in_group order by groname , usename;