Data Definition Language; defining database structures but not their contents.
0
votes
1answer
46 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 ...
0
votes
1answer
38 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?
2
votes
2answers
186 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 ...
3
votes
1answer
116 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 ...
1
vote
1answer
71 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
votes
1answer
280 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 ...
3
votes
3answers
219 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
...
3
votes
1answer
788 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 ...
1
vote
1answer
122 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 ...
1
vote
0answers
30 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
votes
2answers
685 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 ...
7
votes
1answer
133 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?
3
votes
2answers
392 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 ...
2
votes
1answer
189 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 ...
0
votes
3answers
1k 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
votes
2answers
151 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 ...
2
votes
2answers
472 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 ...
5
votes
1answer
313 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 ...
8
votes
2answers
2k 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 ...
7
votes
4answers
571 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 ...
1
vote
3answers
559 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 ...
3
votes
1answer
1k 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 ...
6
votes
1answer
1k 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?
3
votes
1answer
5k 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 ...
11
votes
4answers
992 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 ...
