What is the use of reorg command in IBM DB2 ?
What is the use of reorg command in ibm db2 db ? What does Reorg do internally ? Is it necessary to run reorg if new indexes are created on the table ? ...
ddl
MySQL column drop taking a long time, but not in isolation
I'm dropping a column from a large InnoDB (actually Percona XtraDB 5.1.47) table of 136 million rows taking about 35Gb on disk. To test the process, I copy the entire table (using create table... select), which only takes 14 minutes. I then drop the column, which only takes 6 minutes. If I do exactly the same operation on the original ta...
Easiest way to drop and recreate existing views with SCHEMABINDING in T-SQL
I'm interested in the most elegant way of creating a solution using all T-SQL that will allow me to target any view that is currently in the database using the WITH SCHEMABINDING option and dropping it then recreating it. We need to assume here that we don't have access to the original DDL scripts so the Views must be recreated only from...
MS Access: setting table column Caption or Description in DDL?
Is it possible to set a table column's Caption or Description properties in MS Access using DDL? Ideally, we could include them in a CREATE TABLE statement. ...
Automating DDL in .Net
I maintain a product that has a home-grown data access layer, programmed in C++/COM for use with a Windows-based web application designer and it is at least 10 years old. This DAL is modularized with something akin to Data Providers so that it can generate basic SQL and DDL for a specific database type. In the process of migration anal...
Is there a good way to verify if a database schema is correct after an upgrade or migration?
We have customers who are upgrading from one database version to another (Oracle 9i to Oracle 10g or 11g to be specific). In one case, a customer exported the old database and imported it into the new one, but for some reason the indexes and constraints didn't get created. They may have done this on purpose to speed up the import process...
How can I create a disabled trigger in SQL Server 2005?
When using Oracle you can create a disabled trigger specifing the word DISABLE before the trigger body. How can I achive the same effect in Sql Server? ...
GRANT DDL in Oracle to specific user
Hi, How to grant DDL privileges in oracle ? On database I've users SCHEMA_1, SCHEMA_2 and SCHEMA_3 and now i want to from schema_1 be able to do DDL only on SCHEMA_2 Is the grant is possible from SCHEMA_2 level or system only ? ...
Adding constraints is DDL or DML
Adding constraints in sql server comes under which category DML or DDL? ...
How do I extract Sybase (12.5) table DDL via SQL?
I've scanned similar questions but they seem to be referring to other databases and/or external languages. I'm looking to programatically extract table DDL via SQL, with a result that's "good enough" to re-import and reconstruct the table. DBArtisan produces the exact result I'm looking for, but I have a dynamic list of a few dozen t...
Oracle Script problem - create trigger not terminating
I am trying to make some changes to an oracle database and have a script put together to do so. The problem is when it gets to a point in the script where I am creating a trigger it seems like the Create Trigger block does not properly terminate, when I look at the trigger afterwards it contains all of the remaining code in the script. ...
How can dbms_metadata from Oracle produce constraints seprate from the table schema files?
Why is dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE) not generating constraints in separate files? At this moment this pl/sql pastes the constraints after the table schema definition files. What flag do I have to use to achieve this separate constraints schema definition directory? ...
Which Oracle view contains all constraints together?
I'm trying to get CONSTRAINTS from user_objects table like this: select CASE object_type WHEN 'DATABASE LINK' then 'dblinks' WHEN 'FUNCTION' then 'functions' WHEN 'INDEX' then 'indexes' WHEN 'PACKAGE' then 'packages' WHEN 'PROCEDURE' then 'procedures' WHEN 'SEQUENCE' then 'sequences' WHEN 'TABL...
What is Derby's equivalent of DB2's "NOT NULL WITH DEFAULT"
I'm investigating how feasible it is to change some unit tests (for an application running on DB2) to use Derby. Having found this question where the answer claims that DB2 and Derby are very compatible, it seemed like a possibility to take DDL out of the DB2 database and run it on a Derby database. But I seem to have found a case where ...
SQL2008 R2 Capturing Dynamic Sql through DDL Trigger
I have setup a DDL trigger @ Database level to capture all DDL changes and to log them into a Table. When I issue a Dynamic SQL statment such as set @sql="SELECT * INTO __Temp__XLPriceList FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source="\\DEV-v1\d$\DEV-V1\EXCEL_BOOKS\EXPRESSLANE\DISCOUNTS\08Oct10-DISCOUNT-010-141.XLS"; ...
Django create/alter tables on demand
Hi All, I've been looking for a way to define database tables and alter them via a Django API. For example, I'd like to be write some code which directly manipulates table DDL and allow me to define tables or add columns to a table on demand programmatically (without running a syncdb). I realize that django-south and django-evoluti...
Entity Designer Database Generation Power Pack generates datetime and not datetime2 for ProviderManifestToken = 2008
Im having trouble with the DDL-generation from EDMX using the "Entity Designer Database Generation Power Pack" add-on. It generates datetime-columns instead on datetime2 even though ProviderManifestToken is set to 2008. Can it be fixed? I don't want to do this manually all the time because Im using the model-first-approach... ...
How can I include database changes (DDL patches, one-time data inserts, etc) in my build process?
I run my build script and then I have to remember which of the database SQL and PL/SQL scripts to run each time I deploy my application. How can I include these patches in my build script? Or does everybody just run them manually? Currently I number my patches so I know the order to run them, but sometimes I have to check SVN history ...
SQL CLR DDL trigger written on C#
Is it possible to create a database/DDL trigger in C#? And, if so, then how? [EDIT] Basically, I decided to use CLR for the database trigger because I want to utilize C# SMO to script the objects that change and insert the object script into a table that tracks versions of database objects. ...