I'm trying to move a Windows SQL Server database from Windows 10 to Linux. For this, I'm following the instructions in https://learn.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-database?view=sql-server-linux-2017 The linux database is freshly installed in an Ubuntu 16.04.4 LTS. The Windows database backup is a previously existing (I have not executed the backup, but it's a full backup) When I try to restore, it generates an error in the catalog database (access denied), as shown (database name changed to 'mydb' for privacy): sqlcmd -S localhost -U SA -Q "RESTORE DATABASE mydb FROM DISK = '/var/opt/mssql/backup/mydb_backup_201804300000.bak' WITH MOVE 'mydb' TO '/var/opt/mssql/data/mydb.mdf', MOVE 'mydb_log' TO '/var/opt/mssql/data/mydb_log.ldf', MOVE 'sysft_appuser_catalog3' TO '/var/opt/mssql/data/catalog.ft'" Msg 7610, Level 16, State 1, Server irulan, Line 1 Acceso denegado a '/var/opt/mssql/data/catalog.ft' o la ruta de acceso no es válida. Msg 3156, Level 16, State 50, Server irulan, Line 1 El archivo 'sysft_appuser_catalog3' no se puede restaurar en '/var/opt/mssql/data/catalog.ft'. Utilice WITH MOVE para identificar una ubicación válida para el archivo. The other 2 files (mdf and ldf) are created without problems in the same folder). I have tried with different file names, creating previously (touch) the file, and so on with no success. How can I restore this database? I'd be willing to restore it without the full-text index - is there a way to do that? This is the output of FILELISTONLY (to check the content of backup) LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint SnapshotUrl -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ mydb D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb.mdf D PRIMARY 3460300800 35184372080640 1 0 0 D64B0490-3FF6-4EFE-A9A1-491B5993F3AF 0 0 2348613632 512 1 NULL 30094000017824000037 B7E468AB-78C2-4732-8D73-2F07E3ABAF9D 0 1 NULL NULL mydb_log D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb_log.ldf L NULL 1540227072 2199023255552 2 0 0 A6B8CF28-C3D8-4B50-B030-4D5B14F82084 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL sysft_appuser_catalog3 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\appuser_catalog3 F PRIMARY 931744 0 65539 17974000000690900001 0 0B0AEAB0-86A2-42ED-9B37-E70EE556383C 0 0 983040 512 1 NULL 30094000017824000037 B7E468AB-78C2-4732-8D73-2F07E3ABAF9D 0 1 NULL NULL (3 rows affected) Note: I have found this [Stack Overflow post](https://stackoverflow.com/questions/536182/mssql2005-restore-database-without-restoring-full-text-catalog) ; the poster had a different problem, but was also willing to restore the DB without the full-text data. It doesn't say how (if!) he ever resolved his problem, so it doesn't really give me an answer.

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 with my query I need to actually know *what* in a given scenario would throw it off on the explicit context of having a query or queries to run on a purely test database/table to modify that database/table so run this query on in order to test for any false-positives. SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod), CASE WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial' WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial' END AS type FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'delete2'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary; Here is a table with a primary key that does not return the primary key with this query: CREATE TABLE delete_key_bigserial ( test1 integer, id bigserial NOT NULL, col1 text, col2 text, test2 integer );

I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any help is appreciated > The SELECT permission was denied on the object 'database_principals', > database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, > Error: 229)

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 script to do this? Thanks!

I've created a few new user defined types (UDTs) in PostgreSQL. However, now I have two problems: 1. how to see which UDTs have been defined? 2. how to see the columns defined within these UDTs? Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.

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 return a list like: Column Name | Column Type =========================== First Name | character Last Name | character Age | integer Date of Birth | date I have looked up the information_schema views described in an answer below and it seems to cover tables quite well, and I suspect it covers views as well but I haven't checked that yet. The last is any arbitrary but valid SELECT query eg involving, JOINS, UNIONS etc, on the database. Is there a built-in procedure, or other stored procedure or script that can return the same for any valid QUERY at all? I am developing a program that creates data and querying forms and the information is needed for data validation and executing functions on the returned data.

I am trying to see if a specific column exists in any functions/sequences. I have found a script which can search tables, is there anything out there that would allow me to search in functions/sequences (generally anywhere ideally)?

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'product_id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
order by t.table_schema;

The above can show me views/tables that have a product_id column. Currently working with Postgres 12. Can't seem to find one that would list for functions/sequences.

Is there a "elegant built-in" case-insensitive way to check if db is exists? I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check. The first thing that comes to mind to retrieve all db names and filter them by hand, but I think there is more elegant way to do it.

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 people to manipulate SSIS Catalog projects.

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 form ceu_shard_test_merge_*, to use shell globbing terminology. So, conceptually, this can be written as DROP SCHEMA ceu_shard_test_merge_* CASCADE; by analogy with a Unix shell. Of course this command doesn't work, so how can one do this with a single command? My understanding is that this cannot be done portably. I'm using PostgreSQL 8.4, but methods for more recent versions of PG are fine too. It would be nice if the solution had a dry run or dummy option, so one could see what commands were going to be run before actually running them. Perhaps a way to just print the commands? Also, an indication of how to deal with more general patterns than the example given would be nice.

I am writing queries which read identical tables across databases and schemas and I need the columns to include which schema and table a row comes from. While searching during the creation of this post I came across [this answer](https://stackoverflow.com/a/20576324) SELECT u.*, n.nspname as schema, u.tableoid::regclass::text as table_or_view FROM uses u JOIN pg_class c ON c.oid = u.tableoid JOIN pg_namespace n ON c.relnamespace = n.oid This now includes the schema and the table or view. What joins do I need now to add the database as well?

Consider the following scenario: CREATE DOMAIN dom_zipcode AS text; ALTER DOMAIN dom_zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5); Now, if I want to drop that constraint with ALTER DOMAIN , The manual says : ALTER DOMAIN name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] But how can we find constraint_name? \dD only shows the constraint's definition (CHECK statement). \dD dom_zipcode ; List of domains Schema | Name | Type | Modifier | Check --------+-------------+------+----------+-------------------------------- public | dom_zipcode | text | | CHECK (char_length(VALUE) = 5) (1 row) I can dump the schema using pg_dump, but I believe there must exist a more elegant way to establish this using the psql terminal.

I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the schema name. For example, if a table tt is in a non-public schema ex, the text value of the regclass is the same as the original: => select 'ex.tt'::regclass::text; text ------- ex.tt When it's in public, the schema name is lost: => select 'public.tt'::regclass::text; text ------ tt Is there an way to disable this behavior, or to convert to text without losing the schema name?

In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??). CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, col_name text) RETURNS bool AS $func$ SELECT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema=get_schema($1) AND table_name=get_table($1) AND column_name=$2 ); $func$ LANGUAGE sql; So if the table name is 'staging.my_table'::regclass, I'd like to get staging from an imaginary function get_schema. *Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?* *In addition, is it guaranteed that the table name _tbl, when converted to text, will always have a schema name? (i.e. not omitting things such as public.)* I'm not very familiar with the regclass type. I searched but couldn't find how to extract the schema name, and just wanted to ask first before re-inventing wheels.

I am learning about PostgreSQL and am trying to understand basic concepts like schemas, catalogs and tables. I have been reading _PostgreSQL: Up and Running_, and to quote the book: - Schemas are ... the immediate next level of organization within each database, [they] organize your database into logical groups. - Catalogs are system schemas that store PostgreSQL builtin functions and metadata I am currently reading about pg_database , and am confused whether it's a catalog or a table. From the documentation , it says "The **catalog** pg_database stores information about the available databases". But on pgAdmin4, it is listed as a table within the pg_catalog catalog. In the book, there's a query that updates the datistemplate column as if it's a table.

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

So is pg_database a catalog or a table? Or is it (somehow) both?

I'm querying to the system catalog in Postgresql 9.6.4 Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to find the type of index anywhere in the system catalogs. How can I query the catalogs to get a list of indexes along with their type?

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. However, I don't know what schema will be holding the tables. DO LANGUAGE plpgsql $$ BEGIN PERFORM 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = current_setting('search_path') AND c.relname = 'foo' AND c.relkind = 'r'; -- not sure if I actually need this or not... IF NOT FOUND THEN RAISE 'This application depends on tables created by another application'; END IF; END; $$; However, current_setting('search_path') returns a TEXT containing "$user",public by default, which isn't terribly useful. The only other thing I can think of is to try selecting from the table and catch the exception. It would do the job, but I don't think it is very elegant and I've read that it is *expensive* to use (though maybe that would be ok in this scenario since I'm only running it once?).

I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering. Given a table name, is it possible to get a list of the names of the columns in that table? For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.

PostgreSQL 12 _When I rename a column in a table, I would like to dynamically rename the matching check constraints as well._ I have a hypothetical table:

CREATE TABLE hypothetical_table (
  id SERIAL PRIMARY KEY,
  some_col text,
  some_col_also text
);

There are constraints:

ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_length_>_5 CHECK(char_length(some_col) > 5);

ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_also_length_>_5 CHECK(char_length(some_col_also) > 5);

I do not know ahead of time what constraints a column may have. I do know that they are prefixed with the column name. But, like some_col constraint above, that prefix may match another column's (some_col_also). When I rename a column in this table, I would like to rename the constraints as well, to match the new column name. So, if I were to rename some_col, how can I select all of the check constraints associated with that column? I have tried joining pg_catalog.pg_constraint with pg_catalog.pg_attribute ON con.conkey = pga.attnum (with conkey unnested), but that returns many constraints that are not associated with that column.

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and **does not** have access to schema_information.tables because when I try the following script: SELECT table_name FROM information_schema.tables WHERE table_schema='{schema}' I get a return of 0, as I should since this user does not have permission. I am trying to query the database to verify the tables and the columns created. I can get the list of table names through the system catalog with the following script: SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = '{schema}' And this outputs the table names the way I want it: > business, location, person, etc... I can't find the script with system catalog to then find the column names (**And as a bonus, the data type**) of each table. So far I've tried the following: SELECT attname, format_type(atttypid, atttypmod) AS type FROM pg_attribute WHERE attrelid = 'business' and here is the error: ERROR: invalid input syntax for type oid: "business" LINE 1: ...od) AS type FROM pg_attribute WHERE attrelid = 'business' ^``` Also tried: SELECT a.attname as "Column", pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype" FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**' --AND pg_catalog.pg_table_is_visible(c.oid) ); and this returns 0 with a schema or database. I'm not sure what to put there for c.relname. Am I also seeing 0 with this because a basic user just cannot see deeper than tables in a schema, period?