The tag has no usage guidance.

learn more… | top users | synonyms

74
votes
3answers
145k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
10
votes
2answers
5k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
7
votes
2answers
3k views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error....
6
votes
3answers
43k views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
4
votes
3answers
6k views

Permission to view execution report in SSIS Catalog

We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin? This is for production environment and we don't want ...
4
votes
1answer
3k views

Restore All Databases Script

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample ...
4
votes
2answers
15k views

db2 database alias not listed via list database directory but still unable to create that database

I am trying to create a database by logging on to the linux server on which the db2 server is installed, as the instance owner db2 => list database directory System Database Directory Number ...
3
votes
1answer
2k views

PostgreSQL SELECT primary key as “serial” or “bigserial”

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
3
votes
3answers
119 views

Oracle data dictionary, tell built-in roles apart from custom roles

In Oracle, if you describe the DBA_ROLES view, you only get three columns: SQL> describe dba_roles; Name Null Type ------------------------------------------------ ROLE ...
3
votes
2answers
78 views

disallow SET command in a postgresql server

Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
2
votes
2answers
2k views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
2
votes
1answer
1k views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
1
vote
2answers
6k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
1
vote
1answer
53 views

Rebuild the Fulltextcatalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this 30 hours. Our business users ...
1
vote
1answer
33 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
1
vote
1answer
97 views

PostgreSQL Primary key disappears from test table

I have a somewhat convoluted scenario where a test table I had created with a primary key won't return the primary key. pgAdmin III reports there are no constraints. I have the entire PostgreSQL query ...
1
vote
0answers
15 views

What are all the catalogs that RDBMS have to implement to be ANSI compliant?

My goal is to be able to go into any environment, and immediately get a general idea of what is going on in a database server. I know most RDBMS implement INFORMATION_SCHEMA, which has a lot of useful ...
0
votes
1answer
297 views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...
0
votes
2answers
2k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
0
votes
0answers
130 views

Full text catalog missing a keyword

We have a full text catalog on one of our databases but one keyword does not work. e.g: select * from table where name = 'jon' -- result set returns as expected select * from table where ...
0
votes
2answers
1k views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
0
votes
0answers
361 views

SQL Server full text indexing disabled

I have a SQL Server 2008 R2 server instance where I'm unable to use full text catalogs. It was upgraded from SQL Server 2008. When I go to the database properties of an existing database or try to ...