Questions tagged [ddl]

Data Definition Language; defining database structures but not their contents.

Filter by
Sorted by
Tagged with
0 votes
1 answer
44 views

DB2: Working with concurrent DDL operations

We are working on a data warehouse using IBM DB2 and we wanted to load data by partition exchange. That means we prepare a temporary table with the data we want to load into the target table and then ...
0 votes
1 answer
164 views

MySQL/Aurora increase column size behavior

I would like to increase the size of a column. The column currently is varchar(1000) and will be changed to varchar(2000). Null value is already permitted. The tables has about 2 millions of records, ...
  • 101
0 votes
0 answers
58 views

Is it an option to deploy specific schema with objects - procedures, funtions, views to multiples databases?

We have schema utils which we would like to deploy with database objects (procedures, functions, views) to multiple databases on different servers via Azure Devops Pipeline. Is there any option to ...
  • 257
1 vote
1 answer
34 views

mysql optimization for intensive schema-altering queries (DDL)

In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now). ...
0 votes
0 answers
63 views

Is there a command line tool to convert a SQL DDL file describing tables and relationships into a diagram?

Is there a command line tool that takes one or more SQL DLL files describing entities and relationships, and then transforms it into an ER diagram or something textual that can be turned into an ER ...
  • 111
1 vote
2 answers
649 views

Get postgres partitioned table structure by a select

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d <table>. All I find so far is by doing pgdump. db=> \d ...
  • 437
0 votes
1 answer
14 views

What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?

I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
0 votes
2 answers
313 views

How to drop all indexes not used for primary keys or constraints

I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added. Is there a way to drop all indexes except the ...
  • 199
1 vote
1 answer
278 views

Postgresql 12 - ddluser and dmluser for same schema dml permission denied on tables [duplicate]

I have created a postgres 12 instance and executed the below command sequence: psql --host localhost -U postgres -c "CREATE DATABASE mydb" psql --host localhost -U postgres -d mydb -f /tmp/...
  • 111
0 votes
0 answers
52 views

Is it possible to run a query for EVERY DDL change that occurs in a server?

I want to run a query on each and every database that is change in whatsoever DDL change possible. For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
  • 179
0 votes
1 answer
233 views

Oracle generating hash_val from a clob

I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated. Can this be done perhaps by making the column hash_val a ...
  • 103
2 votes
0 answers
161 views

When/how does removing an index cause a table to lock in MySQL?

Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html. As I understand it, INPLACE means that there is potentially some ...
1 vote
1 answer
284 views

How to parametrize DDL in a plpgsql procedure?

How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
1 vote
1 answer
3k views

PostgeSQL: create role/user if not exists?

Is there some way to create a user/role/group in PostgreSQL via SQL only if it does not exist yet? The only solution I have found so far is creating a custom stored procedure, since that supports ...
5 votes
1 answer
1k views

Disabling triggers makes DELETE fast, but the table has no triggers?

I am deleting 130k out of 750k rows from a PostgreSQL table. The first time, it took 8 hours to complete the delete query. Second time, I added an index to the table, and rebuilt that index. Now it ...
1 vote
1 answer
1k views

ERROR 1180 (HY000): Got error 5 during COMMIT

We're using Galera cluster and I'm running below statement to make a new table: create table tab2 select distinct id , report from my_table ; It runs for sometime and after that it fails/cancel ...
  • 326
2 votes
2 answers
226 views

Convert postgres varchar[] to text[] without downtime

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes. Is there a way to manage this conversion without the ...
  • 253
3 votes
1 answer
539 views

Postgres varchar(100)[] -> text[] column conversion

I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
  • 253
1 vote
1 answer
374 views

Can I make postgres' pg_dump condense the alter table statements in the create table section?

If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this: CREATE TABLE public.account ( id uuid NOT NULL, db_version ...
0 votes
1 answer
118 views

Online schema changes for cloudsql DB

I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime. What I have tried so far: I tried with pt-online-schema-...
  • 326
2 votes
1 answer
332 views

Retrieve Names of Check Constraints Specific to a Column

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 ...
  • 163
0 votes
2 answers
139 views

Add a column with name as a result from a SELECT

I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. ...
0 votes
2 answers
158 views

Can't rely on INFORMATION_SCHEMA.view_definition?

I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code select * ...
  • 177
1 vote
1 answer
719 views

Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?

We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for ...
-3 votes
1 answer
5k views

What is "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4" for? [closed]

I'm exploring Sakila database. After CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn't see this in my SQL course) ...
0 votes
1 answer
55 views

rename a column without disrupting users

You can rename a column using the following statement: ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; However, this brings the old column out of commission immediately. Any use ...
0 votes
2 answers
2k views

get DDL from Mongo database

I haven't done much with MongoDB. But in trying to understand the data that we do have in MongoDB, it makes sense to start with looking at the DDL in the database. What is the simplest way to pull the ...
  • 1,492
0 votes
1 answer
75 views

is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra

Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?
2 votes
1 answer
87 views

Implications of removing unnecessary column from primary key

I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g. CREATE TABLE [dbo].[SomeTable]( ...
  • 1,157
0 votes
0 answers
39 views

Oracle 12cR2 - ORA-00905 When Creating View

I want to create a view as below: CREATE OR REPLACE VIEW "USERNAME"."VIEWNAME" ("columnName", "columnName", "columnName", ...) AS SELECT DISTINCT ...
  • 35
0 votes
1 answer
134 views

Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions

I have a view in my production database and I want to create same view in my test database. I tried this commmand: ‌‌select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; It ...
  • 35
1 vote
1 answer
36 views

Create A Pivot With No SUM() in MySQL

I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL Create Table `data` ( callDate text ,callerName text ,...
-1 votes
3 answers
28 views

Set The Order Query Results Display

I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
0 votes
1 answer
78 views

Mysql : what are the steps for creating a database schema for a big project with many tables?

I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it? For eg: building the ER model first or something like that
  • 93
6 votes
1 answer
598 views

How to safely replace the old table with its cleaned copy?

I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with DROP TABLE table; ALTER TABLE table_cleaned ...
0 votes
1 answer
69 views

How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

I created a table in HeidiSQL: CREATE TABLE `publish_dt` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `TS` DATETIME NULL DEFAULT NULL, `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
1 vote
1 answer
298 views

How to get SHOW CREATE TABLE without table borders made of dashes?

When I use SHOW CREATE TABLE in my MariaDb command line client I get this output for my table: +---------+----------------------------------------------------------------------------------------------...
0 votes
1 answer
91 views

How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

We are dealing with very large tables in Sql Azure. Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends. Metadata operations are quick ...
  • 446
1 vote
1 answer
476 views

lock-ddl-per-table option not working as expected in Xtrabackup

I've recently been getting the following errors on some of my Percona XtraBackup backups: InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
  • 1,954
1 vote
0 answers
235 views

Is it possible to alter normal column to virtual column in Oracle?

ALTER TABLE TAB_NAME ADD TEST_COL VARCHAR2(255); ALTER TABLE TAB_NAME MODIFY TEST_COL GENERATED ALWAYS as ('some_exp') VIRTUAL; Second script gives an error: Error report - ORA-54026: Real column ...
  • 13
1 vote
1 answer
548 views

Deny All DDL & DML Commands In Sql Server

I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?
  • 59
2 votes
2 answers
273 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
  • 26.7k
7 votes
3 answers
460 views

What events still occur within a transaction that gets rolled back?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a ...
  • 26.7k
8 votes
1 answer
232 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
  • 26.7k
3 votes
2 answers
288 views

Migrate foreign key type from char to binary - ways to deal with the fallout?

I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays). One of the fields ...
  • 334
0 votes
1 answer
5k views

errno: 150 "Foreign key constraint is incorrectly formed" [closed]

CREATE TABLE IF NOT EXISTS `Register_Per` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `DOB` varchar(20) NOT NULL, `Gender` CHARACTER(8) NOT NULL, `ConNo` int(11) NOT NULL ...
0 votes
1 answer
87 views

Strategy for making Postgres support multiple versions of client code

I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on. The setup here is that we've got a lot of software deployed with a non-...
1 vote
1 answer
610 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
0 votes
1 answer
538 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
  • 117
0 votes
1 answer
2k views

creating a new table results in incorrect syntax error [closed]

i'm trying to create a blank table which already exists in another SQL instance. so i just right clicked on the table and choose "SCRIPT CREATE TO" to export the structure to a query window, and it ...
  • 21

1
2 3 4 5 6