How to rename a column in SQL?
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. ...
ddl
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? ...
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 the table and field names and whether tha...
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) on update cascade); The client can input ...
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. But I am thinking, any way to inverse t...
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. ...
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's DBMS_METADATA stuff. Also, preference f...
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 and DELETE only. Is it possible to log who ...
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.... ...
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 production database. Is there a way to do it...
Dropdownlist select
I have a dropdown list that searched a column in database table and returns items to listbox in DDL's selectchange event. In this same event I want to include code that will populate teh listbox with items that where in it before search if user deselect , i. e. if ProjDDL.selecteditem.value = "--". Please help me with the code. protect...
DropDownList in C#, getting DropDownList items overflow after every time using selecting an item
Hello all, well the problem is that i am trying to get DDL to: 1. Recive catagories from a DB tabel - working 2. OnChange select from a different table the products by the item in the DDL - working had a problem with No1 but fixed that problem. i found out that to get No1 working i have to use postback. did that and every thing in that p...
How can I programmatically retrieve the alter view script for a view in SQL Server 2005.
We allow our uses to alter certain views for reports and what not based on some application field meta data that we keep track of in our application. These fields can be created at run time. I have a standard process in place to alter the views when a field is added or removed. I now need to do this programmatically however, which mea...
Flag column or foreign key?
I have ENTERPRISES and DOMAINS table. The property of each enterprise is that it should have a single primary domain, but it can have more than one domain. I have come up with this table structure +---------------------------------------+ | ENTERPRISES | +----+--------------+-------------------+ | ID | Name ...
SQL Server: How to generate object scripts without DMO/SMO?
i want to generate scripts for database objects, e.g. tables views stored procedures functions Since: SQL Server Management Objects (SMO) SQL Distributed Management Objects (SQL-DMO) (depricated) are not installed on a fresh install of: Windows XP Windows Vista Windows 7 nor are they redistributable, they are not an option (...
How to use Nhibernate Validator + NHib component + ddl
I just configured my NHibValidator. My NHibernate creates the DB schema. When I set MaxLenght="20" to some property of a class then in the database the length appears in the database column. I am doing this in the NHibValidator xml file. But the problem is that I have components and cannot figure out how to achieve this behaviour. The co...
Generate DDL programmatically on Postgresql
Hi, How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers. ...
How do I DROP a constraint from a sqlite (3.6.21) table?
I have the following table: CREATE TABLE child( id INTEGER PRIMARY KEY, parent_id INTEGER CONSTRAINT parent_id REFERENCES parent(id), description TEXT); How do I drop the constraint? sqlite> ... ? ...
How do I add a foreign key to an existing sqlite (3.6.21) table?
I have the following table: CREATE TABLE child( id INTEGER PRIMARY KEY, parent_id INTEGER, description TEXT); How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled. Most examples assume you're creating the table - I'd like to add the constraint to an existing one. ...
Is there a tool to generate a full database DDL for SQL Server? What about Postgres and MySQL?
Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a file for each object, be it a table, a procedure, a view, etc.) so I can write code...