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

learn more… | top users | synonyms

-2
votes
1answer
28 views

Generate create table DDL from any arbitrary select query? [on hold]

Is there a way, given any arbitrary select query like: select myfavoritenum, myfavoritetxt from tablewithmanycolumns and magically receive a create table statement: create table justmyfavorites ( ...
2
votes
2answers
2k views

SQL Server Replication: “ALTER TABLE ALTER COLUMN” is not propagated to subscribers

We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for ...
6
votes
1answer
91 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
0
votes
1answer
25 views

IS-A relation when defining tables

I already asked this question on SO, prior to realizing that there may have been a more suitable place. I am trying to implement an IS-A relationship between tables. Say I have a singer and a band ...
0
votes
0answers
22 views

Slave executing queries with different charset

I need to change a database charset because of this issue and I solved my issue in staging environment with the SQL command ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE ...
2
votes
1answer
255 views

What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

I have a script that shows commands being fired in SQL Server 2008 database as follows: --shows who is active and executed commands declare @SPID int=null, @Status varchar(max) = null, @LOGIN ...
0
votes
1answer
28 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
2answers
38 views

Transaction has set autocommit = 0, but still the update happens before I commit

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of ...
0
votes
1answer
31 views

All users are having NO Privileges in phpMyAdmin

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one ...
0
votes
1answer
53 views

How to quickly increase column size in Oracle?

In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command alter table shipment ...
0
votes
2answers
45 views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
1
vote
0answers
790 views

DDL trigger AFTER ALTER ON SCHEMA behavior

Can anyone please tell me what's going on here: CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk ...
10
votes
2answers
4k views

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
1
vote
1answer
573 views

Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have ...
6
votes
1answer
4k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
0
votes
1answer
52 views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
2
votes
1answer
61 views

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from ...
1
vote
2answers
67 views

Best approach to maintain a single set of DDLs and DMLs for multiple database type

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these ...
0
votes
1answer
48 views

Alter trigger Oracle

Does alter trigger hang if a table is locked by some DML operation ? What kind of exception does it throw? Resource busy 0054 or some other exception...? I have tried to reproduce this scenario but ...
1
vote
1answer
138 views

SQL Server 2012: Give rights to read ddl but not modify?

What rights would I need to be give a user for them to be able to view all DDL code (such as stored procedure code) but not be able to edit any of it? The only possibility seems to be DDLadmin, but ...
1
vote
1answer
67 views

Add a column with Not Null [duplicate]

I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column . When I'm adding this I am getting the following error: Unable to ...
1
vote
0answers
43 views

Is that create table statement correct? [closed]

Although i am not part of this community for long time, i already feel like you are my family, since thats what family does. We help each other. I am thankful to everyone who helped me! CREATE TABLE ...
1
vote
4answers
4k views

What is the purpose of having a table NOT LOGGED INITIALLY

I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY. Example from the book I read: CREATE TABLE products ( productID ...
0
votes
1answer
26 views

Preview the increment in a Mysql table size before running the ALTER TABLE ADD COLUMN statement

Is it possible to preview how much the table size will be incremented by an ALTER TABLE ADD COLUMN statement before running the statement in the database?
2
votes
1answer
172 views

LOCK=NONE when adding NOT NULL constraint fails with “cannot silently convert NULL values” error despite no NULL values being present

I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table ...
2
votes
2answers
160 views

Error on DDL statement never invokes CATCH

When I try to close the SS Management Studio window below is the error I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ...
2
votes
0answers
85 views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
0
votes
1answer
85 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
1
vote
0answers
50 views

SQL Server Comparison

I am merging two different versions of a database. I want to add some specific fields from source database to destination, so I used Visual Studio to do that. It does the job quite well but only table ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
1
vote
1answer
41 views

What's wrong with my DDL statement? [closed]

I am trying to run: CREATE TABLE `members` ( `id` int(11) NOT NULL auto_increment, `users` text NOT NULL, `password` text NOT NULL, `ip` text NOT NULL, `status`text NOT NULL, UNIQUE KEY `id` (`id`) ) ...
0
votes
0answers
47 views

exporting DDL of all views Oracle

How can I go about exporting DDL of all my schema views in single .sql file ? I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case. I know how to ...
2
votes
3answers
2k views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
1
vote
1answer
78 views

Is there a way to create a re-usable base table definition in InfoSphere Data Architect?

Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts. The data architects originally designed several "re-usable" ...
1
vote
1answer
623 views

Check constraint references another column in same table

I have this table but check constraint is throwing me an error: Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'. I've seen this as allowed ...
1
vote
1answer
45 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
5
votes
1answer
140 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
0
votes
2answers
77 views

Modifying a part (one procedure) in oracle database package

Is it possible to modify an individual procedure inside an Oracle DB Package? instead of recreating (modification is not done using a SQL editor, so cannot modify a place and easily recompile the ...
0
votes
0answers
16 views

Managing schema migrations in Mondrian

Managing schema changes across dev, stg, prd is something we do well in our home-grown solution today. Specifically, at least one downstream team consumes our star schema. They're considering ...
1
vote
1answer
113 views

Can I run DDL in pretend mode in Oracle

I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script. For ...
1
vote
1answer
169 views

Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

I am importing from the GUI a CSV file into a table. I have created the following table: CREATE TABLE jira_tickets.daqa_rpt_tbl ( daqa_report_id bigint NOT NULL DEFAULT ...
0
votes
0answers
24 views
4
votes
2answers
2k views

Why is truncate DDL?

I have an interview question, which being asked during my interview. I answered the question, but interviewer was not convinced with my answer. So, please let me know the what would be the correct ...
2
votes
2answers
266 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
0
votes
1answer
2k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
3
votes
1answer
184 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
11
votes
2answers
9k views

Script out Oracle DDL in an automated fashion

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention. I know of DBMS_METADATA.get_ddl(), but have found that the ...
2
votes
1answer
342 views

Permissions to see and script objects, but not alter them?

I am a software developer. I have a production database that I need full ddl read access to, but not any ddl write or execute access. Is there a script I can send my DBA that will grant me full ddl ...
2
votes
1answer
245 views

How to access external DLL's static methods that incorporate output parameters in SQL Server

I know how to load a CLR DLL in SQL Server and access to its static methods which does not incorporate output parameters in their method signatures but what I cannot realize is how I can use methods ...
1
vote
1answer
1k views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT ...