Все вопросы: [ddl]

77 вопросов

похожие теги: ddl-trigger
31
голосов
3ответов
32351 просмотров

Generate table DDL via query on MySQL and SQL Server

Is there an easy way to extract table DDL information, via a query, using either Ms or My SQL server? (preferably both?) For example, using MySQL Administrator / Navicat for MySql, there is a "DDL" function, which generates the "create table foo (....)" script. Is there any way to get this inf...

8
голосов
6ответов
15789 просмотров

T-SQL Scripts to copy all table constraints

I have created many tables on my local database and moved them to production database. Now I am working on fine tuning the database and created many constraints on my local database tables such as PK, FK, Default Values, Indexes etc. etc. Now I would like to copy only these constraints to pro...

0
голосов
6ответов
403 просмотров

Can we create tables dynamically in MySQL?

Can we create tables dynamically in MySQL? If so, how? Dynamic means at run time....ie via procedure AND HOW???? I am using dotnet Ans--> yes we can create...but problem is i want to change the name of table each time the procedure is called....

8
голосов
6ответов
25641 просмотров

Anyway to create a SQL Server DDL trigger for "SELECT" statements?

I am dealing with some sensitive Accounting tables and I would like to audit any SELECT statement executed on the table or any views associated with them. I did not find any DDL Events on BOL (Books Online) that had anything to do with SELECT statement. And DML triggers are for INSERT, UPDATE an...

4
голосов
2ответов
1910 просмотров

SQL Server parallels to Oracle DBMS_METADATA.GET_DDL?

I'm looking for command line or scripted solutions to pull the DDL out of SQL Server 2005+ for all database objects: tables, stored procs, views, indices/indexes, constraints, etc. GUI tools are not of interest. Preference is for built-in tools, since that would be the most comparable to Oracle...

2
голосов
1ответов
2232 просмотров

Ruby / Rails - Reverse Migration - DDL to Ruby Code

Any tools in Ruby or Rails that would allow me to extract from database all the table schema and generate Ruby equivalent "DLL" statements? Something that would allow me to port schema from say Microsoft SQL Server to Postgres, or MySQL to Sqlite.

0
голосов
1ответов
6365 просмотров

Generate Table/View schema from LINQ-TO-SQL DBML file

I'd like to have a single source of the description of the data structure. Some people are asking can the DBML file being refreshed when it is changed in the database. The way I do is stupid but common; open it, delete all, and drag-drop again. I heard that there are some 3rd party do the tricks...

0
голосов
2ответов
114 просмотров

Indexing varchars & Foreign Keys

I have two tables defined below. Create table tickets (id long not null, reseller long not null, constraint pk_lock primary key (id)); Create table ticketRegistrations (id long not null, customer long not null, constraint fkTicketRegistrationTicket foreign key (id) references tickets (id) o...

26
голосов
2ответов
36582 просмотров

How can I create a unique index in Oracle but ignore nulls?

I am trying to create a unique constraint on two fields in a table. However, there is a high likelihood that one will be null. I only require that they be unique if both are not null (name will never be null). create unique index "name_and_email" on user(name, email); Ignore the semantics of...

0
голосов
3ответов
2563 просмотров

How does one extract the definition of a view using standard SQL?

In trying to answer this question for myself I came across this nugget, after eventually adding "oracle" to my query terms: select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL; Which works, but is not portable. How do I do the same thing on MySQL? SQLite? Others?

8
голосов
2ответов
23224 просмотров

How to create a Foreign Key with "ON UPDATE CASCADE" on Oracle?

In MS SQL Server it is possible to create a foreign key with ON UPDATE CASCADE option, so whenever you update one of the columns in the primary key, the foreign keys in other tables will also be update by the DBMS. So, how to do it in Oracle?

23
голосов
2ответов
15636 просмотров

Renaming a column in MS SQL Server 2005

What is the best practice when it comes to renaming a table column using SQL (MS SQL Server 2005 variant)? This assumes that there is data in the column that must be preserved.

2
голосов
1ответов
1832 просмотров

Clone a Table's definition with Hibernate (hbm2ddl)

In my hibernate application there is annotation driven object: AuditEvent. Its very simple and has no foreign key relationships. I archive old entries in this table by moving them to another table OldAuditEvent, which is a clone of the AuditEvent table. Right now we generate the DDL for the ...

0
голосов
5ответов
179 просмотров

Simulate a table creation with SQL

Is there a standard way to simulate a table creation in a database by using SQL? I don't want the table to be created, just check if it could be created. One way would be to create it and then delete it again. Any other way?

1
голосов
1ответов
9324 просмотров

To set a default value to a column in a database by PostgreSQL

I am doing my first database project. I would like to know how you can have false as the default value for the following SQL -query ... MODERATOR_REMOVAL boolean NOT NULL ... Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_...

1
голосов
3ответов
159 просмотров

To understand a sentence about the case in SQL/DDL -queries

This question is based on this answer. What does the following sentence mean? Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them. Does it mean that I should change the commands such as CREATE TABLE, USER_ID and NOT NULL to lowercase? - I...

2
голосов
1ответов
5977 просмотров

Define default date value in MySQL, similar to timestamp

I'm using MySQL (nobody's perfect), version 4.1 and I'm used to define some timestamp columns like that: ALTER TABLE foo ADD creation TIMESTAMP DEFAULT NOW() ; I'd like to do exactly the same thing, but for a DATE field. The reason being I don't need a TIMESTAMP precision and since no function...

0
голосов
1ответов
453 просмотров

Using Schema Export Efficiently

We are using NHibernate as our ORM framework. We have a need to persist classes we load at run time. We do that according to metadata they come with, that holds names and types of the data they have. In order to build the tables for them at run time, we use the SchemaExport class from the NHibern...

2
голосов
4ответов
112 просмотров

How should an empty title be checked in a database?

I am doing my first database project. I would like to know why you should use NOT NULL in the following query ... TITLE nvarchar(60) NOT NULL .. Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) ...

1
голосов
5ответов
1029 просмотров

To prevent the use of duplicate Tags in a database

I would like to know how you can prevent to use of two same tags in a database table. One said me that use two private keys in a table. However, W3Schools -website says that it is impossible. My relational table alt text http://files.getdropbox.com/u/175564/db/db7.png My logical table alt tex...

-1
голосов
2ответов
462 просмотров

Is there any ISO standard of how to write SQL queries in DDL?

I am doing my first database project. I would like to know which of the following ways should I use to make SQL queries in DDL. #1 CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, dateofbirth DAT...

1
голосов
1ответов
451 просмотров

To improve SQL-queries in DDL

Improvements done nvarchar(5000) -> nvarchar(4000) BUT no nvarchar in PostgreSQL => TEXT memory limits to some variables the syntax slightly changed to more readable dashes to underscores Magnus' improvements I am following my plan for my first database project. I would like to know an...

11
голосов
3ответов
10529 просмотров

SQL Server ALTER field NOT NULL takes forever

I want to alter a field from a table which has about 4 million records. I ensured that all of these fields values are NOT NULL and want to ALTER this field to NOT NULL ALTER TABLE dbo.MyTable ALTER COLUMN myColumn int NOT NULL ... seems to take forever to do this update. Any ways to speed it u...

1
голосов
4ответов
210 просмотров

MYSQL: How can I create a MySQL table so, that a field's default value is the output of a function

QUESTION How can I create a MySQL table so, that a field's default value is the output of a function. Can it be done without an insert trigger? BACKGROUND: Some entities and the groups they belong to need to be observed on a daily basis. To do that I created the following MySQL table: CREA...

14
голосов
5ответов
5573 просмотров

Ограничения на изменение схемы PostgreSQL внутри транзакций?

Мой опыт работы с базами данных связан с Oracle, поэтому я был удивлен, обнаружив, что Postgres включает изменения схемы в транзакции - если вы начнете операцию, создадите таблицу, а затем откатитесь, таблица исчезнет. Он также работает для добавления и удаления столбцов. Очевидно, это очень при...

0
голосов
2ответов
264 просмотров

Почему Oracle извергает плохие метаданные таблицы?

Я использую DBVisualizer для извлечения DDL из базы данных Oracle 10.2. Я получаю нечетные экземпляры повторяющихся столбцов в ограничениях или повторяющиеся ограничения в сгенерированном DDL. Сначала я списал это на ошибку в DBVisualizer, но я попытался использовать Apache DDLUtils против БД, и...

1
голосов
5ответов
6196 просмотров

Как я могу создать ddl для моих объектов jpa из кода Java?

Я ищу способ создать ddl для моих аннотированных объектов jpa. Я предпочитаю для этого чистый Java-способ. Если возможно, было бы неплохо также сгенерировать операторы drop.

4
голосов
4ответов
19520 просмотров

Невозможно добавить столбец «NOT NULL» в пустую таблицу в SQL Server

Я понимаю, что при добавлении столбца в таблицу, содержащую данные на сервере SQL, столбец должен иметь параметр NULL или значение по умолчанию. Иначе чем бы SQL Server заполнял новые строки? Я не понимаю, почему я не могу добавить столбец NOT NULL в пустую таблицу. Я пробовал это на двух экз...

30
голосов
4ответов
22791 просмотров

Можно ли запустить несколько операторов DDL внутри транзакции (в SQL Server)?

Мне интересно, можно ли запускать несколько операторов DDL внутри транзакции. Меня особенно интересует SQL Server, хотя ответы с другими базами данных (по крайней мере, Oracle, PostgreSQL) также могут быть интересны. Я выполнял некоторые операции "CREATE TABLE" и "CREATE VIEW" для созданной т...

98
голосов
14ответов
289705 просмотров

Как использовать СОЗДАТЬ ИЛИ ЗАМЕНИТЬ?

Правильно ли я понимаю, что CREATE OR REPLACE в основном означает «если объект существует, отбросьте его, а затем создайте в любом случае?» Если да, то что я делаю не так? Это работает: CREATE TABLE foo (id NUMBER, title VARCHAR2(4000) DEFAULT 'Default Title') И это не так (ORA-00922: ...