Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
2
votes
1
answer
11k
views
create database table with a owner in my SQL
Can someone tell me what is the SQL command to create a table with specific owner?
- 47
11
votes
3
answers
37k
views
Why can't we write ddl statement directly into the PL/SQL block
Why can't we write ddl statements directly in PL/SQL block, for example when i write
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name; // error
END test;
/
But,
CREATE OR ...
- 1,545
16
votes
3
answers
32k
views
Why is truncate DDL?
I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?
Q....
- 1,545
1
vote
0
answers
220
views
Replication issue - CREATE SELECT alternative?
I've an MySQL 5.1 slave for our BI team.
They need to make some CREATE SELECT with big select queries (several million lines).
As CREATE SELECT is a DDL, if the replication attempts to update some ...
- 3,505
2
votes
1
answer
113
views
How to create an object and then alter it
I'm using sql server 2012:
I would like to script out creation of an object(if it doesn't exist first), and then alter it.
I'm trying to avoid the usual drop/create as it would impact stats. It ...
- 933
37
votes
2
answers
50k
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 ...
- 1,221
5
votes
3
answers
6k
views
Rollback group of DDL statements
Working in SQL Server 2008 R2, I am trying to rollback a set of DDL statements as a group (think of an upgrade script for a database), but am running into trouble.
Take the following code:
begin try
...
- 1,631
7
votes
1
answer
15k
views
How to create a partitioned table based on date?
I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
- 5,333
6
votes
1
answer
1k
views
ERWIN create and use database
I'm trying to generate physical database from my logical model using ERWIN 7.
All is well in the script that is generated automatically, there is only the problem that the model is created in the ...
- 161
2
votes
1
answer
666
views
How would I reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008
I have a customer that has a table in the database with an autonumber id field. The customer uses that id as a user-friendly identifier to a unique record stored in the database. After some time, my ...
- 123
1
vote
1
answer
1k
views
Upgrade production database during software development lifecycle
Background
Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
- 833
2
votes
1
answer
1k
views
How to create database, and add user linked to login?
created a login and a user in the master database and linked that user to the login.
I created a new database, then went to that database and immediately tried to create a user linked to the same ...
- 21
18
votes
2
answers
39k
views
Disabling Checking of Schema On Function/Stored Procedure Creation
I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
- 587
1
vote
1
answer
137
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" ...
- 4,906
6
votes
2
answers
6k
views
Column definition for a SQL Server equivalent to Access BOOLEAN type
The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for ...
- 432
8
votes
3
answers
2k
views
Create function in central database or repeat in each database?
One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
- 377
8
votes
1
answer
251
views
Disallow new UDFs that contain a cursor
Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
5
votes
2
answers
2k
views
SQL Server (2012) non-blocking DDL?
I'm not very experienced with SQL Server, so maybe I'm missing something
My situation is as follows:
Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is ...
user1822
2
votes
1
answer
8k
views
Default values in SQLite3
When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
- 23
5
votes
4
answers
14k
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 ...
- 4,906
4
votes
2
answers
414
views
Can I be notified of a rollback in my DDL Trigger?
I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred?
For instance, with ...
user1219358
3
votes
2
answers
2k
views
What effect does serializable isolation level have on DDL-statements?
I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works fine....
- 644
16
votes
5
answers
55k
views
MySQL - fastest way to ALTER TABLE for InnoDB
I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.
I want to change the name of one of the columns and add a few more indices.
What is the fastest way to ...
- 1,513
15
votes
5
answers
23k
views
Is there a means to set the owner of all objects in a PostgreSQL database at the same time?
The Stack Overflow Q & A 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 swimmingly, however ...
- 1,221
5
votes
1
answer
907
views
If an 'after' DDL trigger causes an error, is the DDL rolled back?
It has been suggested that DDL is logically performed something like this:
begin
COMMIT;
perform any appropriate pre-DDL trigger code;
do the ddl;
perform any appropriate post-DDL ...
- 39.7k
14
votes
4
answers
29k
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 ...
- 315
9
votes
4
answers
1k
views
DDL contention on TempDB
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
- 377
2
votes
3
answers
3k
views
Undo DDL statement on View in Oracle
I Want to undo DDL changes on View, how to do that?
I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If ...
- 1,707
24
votes
1
answer
17k
views
How to use transactions with SQL Server DDL?
I have a login table into which all inserts are done by a single stored procedure.
CREATE TABLE dbo.LogTable(
LogRefnr int IDENTITY(1, 1) NOT NULL,
LogQuery varchar(255) NOT NULL,
LogTime ...
- 12.2k
13
votes
1
answer
40k
views
Add DEFAULT to existing column
In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
- 362
9
votes
1
answer
29k
views
How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?
I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.
I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
kg.
22
votes
4
answers
4k
views
Why won't some DBMS's allow rollback for certain DDL statements?
Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it....
- 441