Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
6
votes
1
answer
3k
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 ...
- 489
1
vote
1
answer
3k
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 ...
- 203
0
votes
1
answer
52
views
Which keyword I should use?
I have two tables:
I1 (A, B, C)
I2 (A, C)
I2 has some tuple of data that I1 doesn't have.
Now I want to create a table I3 that only has the data (A, C) that both exists in I1 and I2.
Which keyword ...
- 39
2
votes
1
answer
2k
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 ...
- 337
3
votes
2
answers
2k
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 ...
- 105
2
votes
0
answers
1k
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 ...
- 21
0
votes
1
answer
1k
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 ...
- 337
1
vote
0
answers
59
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 ...
- 111
0
votes
1
answer
32
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 ...
- 503
1
vote
1
answer
109
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`)
) ...
user50149
3
votes
3
answers
24k
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 ...
- 629
0
votes
1
answer
2k
views
How can I reseed all identity columns?
I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
- 645
29
votes
8
answers
70k
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 tables?...
- 747
3
votes
1
answer
3k
views
Apparantly errorneous Exceeds 8K row length message
In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message:
Warning: The table "CustomerTransactions" has been created, but its maximum row size exceeds the allowed ...
- 133
5
votes
1
answer
15k
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 ...
user37362
2
votes
1
answer
175
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 ...
- 21
5
votes
1
answer
1k
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 ...
user1822
0
votes
2
answers
7k
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 ...
- 149
4
votes
1
answer
5k
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 ...
- 1,075
1
vote
1
answer
610
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 ...
- 247
3
votes
1
answer
2k
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 ...
- 245
1
vote
1
answer
1k
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 'jira_tickets.daqa_id_seq'::...
- 111
3
votes
2
answers
5k
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 ...
- 322
2
votes
1
answer
11k
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
(
...
- 433
3
votes
1
answer
2k
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 [...
- 407
10
votes
1
answer
12k
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,484
3
votes
1
answer
11k
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 DBMS_METADATA.GET_DDL('TABLE','table_name','...
- 433
0
votes
1
answer
1k
views
SELECT INTO with regexp_replace() doesn't write changes into newly generated table
I have been using this query in Postgres 9.1 in order to remove '' from existing data.
Below is a sample of the data before running regexp_replace():
6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''
...
- 31
7
votes
6
answers
63k
views
How To change the column order?
How To change the column order in sql server 2008 R2?
Eg: My table order is Eid------Ename-----Esalary
Know i want this format like Eid---Salary----Ename
Thanks.
user36326
7
votes
2
answers
6k
views
What's the complexity of renaming a column in MySql?
We want to rename a column in our MySql (version 5.5) database with something like this:
ALTER TABLE t1 CHANGE a b INTEGER;
Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
- 173
2
votes
1
answer
916
views
Does a DDL in Oracle affect other transactions?
I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction.
However, does a DDL statement in any way affect ...
- 263
14
votes
2
answers
13k
views
Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?
In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
- 545
5
votes
1
answer
1k
views
Online Schema migration in MySQL : When to start?
I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database.
We're having a single MySQL db, and besides many ...
- 153
57
votes
2
answers
55k
views
MATCH FULL vs MATCH SIMPLE in foreign key constraints
I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs.
The default is MATCH SIMPLE. How do they function?
user32234
1
vote
1
answer
595
views
Understanding the output of \d <table> in psql
Say I run the following command:
\d mtb.big_table
I get the output shown below. My questions are:
What are column modifiers? (what does default now() mean?)
Under Indexes, Why do some indices refer ...
- 2,085
2
votes
1
answer
1k
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 ...
- 123
1
vote
0
answers
3k
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 ...
- 11
1
vote
2
answers
169
views
Making a domain conditional on other values in the table
How can I define or change a domain based on another attribute value?
F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be >...
- 113
3
votes
2
answers
256
views
Is this an example of an Enum (Enumerated Type)
I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm ...
- 31
52
votes
2
answers
59k
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 ...
- 745
7
votes
1
answer
207
views
Is there anything a table-level constraint declaration can do that a column-level one can not do?
Had an exam today. One question disturbed me:
What can a table-level constraint do that a column-level constraint can't do?
My answer was that only a table-level constraint declaration permits ...
3
votes
3
answers
5k
views
if block fails to create temp table in a procedure
I'm attempting to do this in a procedure:
DECLARE @a bit = 1;
BEGIN
SELECT * INTO #aTemp FROM OPENROWSET( ... );
IF @a = 0
BEGIN
SELECT ... INTO #bTemp FROM #aTemp;
END
...
- 1,194
2
votes
1
answer
3k
views
Server Wide DDL Trigger Permissions Issue
I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here
The trigger will log the DDL ...
- 720
5
votes
2
answers
4k
views
Postgres DDL and DML best practices and coding standards [closed]
Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend.
I am looking for something similar to what Google has for programming.
...
- 749
5
votes
1
answer
44k
views
Changing a column data type from datetime to float
I have a table with a float column. All values in this column for all records are NULL.
I want to change the data type to datetime.
I executed the query:
ALTER TABLE MyTable ALTER Column MyColumn ...
- 61
3
votes
4
answers
18k
views
Open source tools for Oracle schema compares [closed]
I'm looking for an open source tool to generate DDL scripts. Many tools call these synchronization scripts. We essentially want to compare schema of different environments (ie: DEV to QA, QA to PROD)...
- 2,072
18
votes
3
answers
86k
views
Reset IDENTITY value
I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
- 333
3
votes
2
answers
15k
views
Is there a fast way to change column type without dropping the clustered index?
I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate ...
- 729
4
votes
2
answers
11k
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 ...
3
votes
1
answer
23k
views
Is there a way to truncate table that has foreign a key?
Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
- 557