21 votes

PostgreSQL - Who or what is the "PUBLIC" role?

While this is true it is not the full picture. Public also acts as an implicit role that other roles belong to and that has its own permissions not always reflected and reported that get inherited. ...
Ruan Malan N's user avatar
18 votes
Accepted

How to add comment to a column in a table in Postgres?

To update comment, don't use alter command. Here is the PostgreSQL syntax: comment on column app_user_bookings.mod is '1# mobile booking, 2# admin booking, 3# web booking, 4# tell call' It ...
Saddam Khan's user avatar
16 votes
Accepted

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (...
joanolo's user avatar
  • 13.4k
14 votes

List all columns for a specified table

psql on PostgreSQL 11+ If you're looking for the column types on a query, you can use psql's \gdesc SELECT NULL AS zero, 1 AS one, 2.0 AS two, 'three' AS three, $1 AS four, ...
Evan Carroll's user avatar
  • 63.2k
12 votes
Accepted

Find all tables without foreign keys in a database

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these: SELECT tbl.name FROM sys.tables AS tbl LEFT JOIN sys....
Sam's user avatar
  • 146
12 votes
Accepted

How can I resolve a database trigger's name with built-in functions?

Database- and server-level triggers are not scoped as "objects" per se (this is why you cannot create them under a schema, and why they don't show up in sys.objects). You can see that these objects ...
Aaron Bertrand's user avatar
11 votes
Accepted

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented ...
Solomon Rutzky's user avatar
11 votes
Accepted

Add [Is Primary Key Column] to this query

Query that adds "is_primary_key" field for each column The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a ...
Solomon Rutzky's user avatar
11 votes
Accepted

using a column that references an object_id in your own table

Do not rely on object_id for your application. My experience is that once an object is created its object_id will be stable for as long as that object exists. The difficulty is in knowing the meaning ...
Michael Green's user avatar
9 votes

List all columns for a specified table

PostgreSQL only This is somewhat hokey but could be a contender if you are looking for the shortest possible SQL: SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{...
oᴉɹǝɥɔ's user avatar
9 votes
Accepted

Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?

Yes, the statement there is inaccurate. I talk about this in this blog post (and complained about it in this ancient Connect item), where I implore people to use the sys catalog views over ...
Aaron Bertrand's user avatar
8 votes
Accepted

How to script statistics in Sql Server? (using T-SQL)

A 2005+ compatible option that supports this subset of the grammar is below. CREATE STATISTICS statistics_name ON { table_or_indexed_view_name } ( column [ ,...n ] ) [ WHERE <...
Martin Smith's user avatar
  • 84.7k
8 votes
Accepted

What is the maximum length of a comment in PostgreSQL?

COMMENT has no impositions other than it fit into a TEXT field (verified in IRC with RhodiumToad), From the docs, Very long values are also stored in background tables so that they do not ...
Evan Carroll's user avatar
  • 63.2k
8 votes
Accepted

Check another session's IMPLICIT_TRANSACTIONS setting

There's no built-in way to achieve this. Vote for a product improvement at Easier identification of Implicit Transactions (archive link until Microsoft migrates User Voice content) In the meantime, ...
Paul White's user avatar
  • 84.1k
7 votes

Copy complete structure of a table

This might be using a jackhammer to put a nail in a wall, but given the broadness of the question I think it is a valid option to mention. If you're using SQL Server 2012 SP4+, 2014 SP2+ or 2016 SP1+...
LowlyDBA - John M's user avatar
7 votes
Accepted

Select orphaned rows (in a generic way)

Quoting the manual: A foreign key must reference columns that either are a primary key or form a unique constraint. So that's not necessarily limited to the PK. But if we start with pg_constraint, ...
Erwin Brandstetter's user avatar
7 votes
Accepted

How to find Stored Procedures With Recompile?

Per database, you can do this: SELECT DISTINCT DB_NAME() AS DBName, SO.name AS SPName, SM.is_recompiled, ISR....
Erik Darling's user avatar
  • 40.8k
7 votes
Accepted

Okay to run sp_refreshview against all views on a regular basis?

there's potential for the underlying entities they reference to change This is the problem, and you should solve it at the source. If you are updating objects in the database frequently enough that ...
Aaron Bertrand's user avatar
7 votes

Does restoring a database in Microsoft SQL Server add objects to Resource Database?

Well, reading the documentation for Resource Database (highlighting mine) The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL ...
Scott Hodgin - Retired's user avatar
7 votes

Does SQL Server's is_nullable mean anything at all?

The answer to your question is yes. That column specifies the default nullability of a data type, which can be important if the user doesn't express a preference e.g. in a CREATE TABLE statement with ...
Paul White's user avatar
  • 84.1k
6 votes

Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

The issue was fixed in SSMS 17.3, however you'll need to load the "applocal" assembly and not the one from the GAC. In addition, in order to be "backwards compatible", the ReadOnlyRoutingList property ...
Sean Gallardy's user avatar
6 votes

How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?

I had similar problem. First turn on this option of SSMS - it is turned off by default: SSMS/Tools/Options/Query results/SQL Server/Results to grid/Retain CR/LF on copy or save Close SSMS, and run ...
Przemyslaw Remin's user avatar
6 votes
Accepted

Find tables with multiple indexes on same column

This query retrieves a complete list of indexes build on the same (list of) table column(s in the same order): SELECT indrelid::regclass::text AS table , idx_columns , indexrelid::regclass::...
Erwin Brandstetter's user avatar
6 votes

How can I resolve a database trigger's name with built-in functions?

I use these two snippets to get database and server-level trigger information from DMVs. I also include disable and enable commands because I often have to look them up. Database-level triggers: /* ...
Zikato's user avatar
  • 4,507
6 votes
Accepted

Measuring how long PostgreSQL waits for locks

You can enable log_lock_waits to get information about sessions that have to wait more than deadlock_timeout for a lock. You can reduce deadlock_timeout to see shorter waits. Other than that, monitor ...
Laurenz Albe's user avatar
  • 51.7k
5 votes

Script to see running jobs in SQL Server with Job Start Time

I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the ...
Tequila's user avatar
  • 189
5 votes
Accepted

Where are filtered-index dependencies listed?

I think this information is stored in sys.sql_expression_dependencies. See the table in MSDN, sys.sql_expression_dependencies: THIS TOPIC APPLIES TO: yes: SQL Server (starting with 2008) no: Azure ...
ypercubeᵀᴹ's user avatar
5 votes

Find all tables without foreign keys in a database

You can make use of the sp_fkeys procedure to get the logical foreign key information. But the limitation is you have to execute is by passing the table name as the input parameter. More info for the ...
SwapnilBhate's user avatar
5 votes

Copy a view from one database to another database on the same server

Basic method: Right click on the view in SQL Server Management Studio Save as script to new query window Change context to target database Execute script This is basically correct, but this will not ...
Poldi's user avatar
  • 181

Only top scored, non community-wiki answers of a minimum length are eligible