Tag Info

Hot answers tagged

5

Option 4: Class Table Inheritance This is a design technique used to implement a class/subclass situation. In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant questions and answers. This has the advantage eliminating NULLS (mostly), ...


4

Execute this for all of the tables: alter schema2 transfer schema1.table1; To programmatically get the statements to execute: declare @SourceSchemaName sysname, @DestinationSchemaName sysname, @AlterStatements varchar(max); set @SourceSchemaName = 'NewSchema'; set @DestinationSchemaName = 'HumanResources'; set @AlterStatements = ''; select ...


3

Assuming destination schema is empty: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ALTER SCHEMA Schema2 TRANSFER Schema1.' + QUOTENAME(t.name) + ';' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'Schema1'; PRINT @sql; -- EXEC sp_executesql @sql; If the destination schema is not empty, ...


3

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of. Example Query: SELECT CT.site1, CT.site2, S1.site_description, S2.site_description FROM circuit_table AS CT INNER JOIN site_table as S1 ON CT.site1 = S1.ID_site INNER JOIN site_table as S2 ON CT.site2 = S2.ID_site


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...


2

so new tables will be affected in the future? No, to affect new tables set DEFAULT PRIVILEGES: PostgreSQL CREATE TABLE creates with incorrect owner Note that default privileges are set per role.


2

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' --counts all tables SELECT COUNT(*) FROM information_schema.referential_constraints --counts all FK relationships


2

is this what business process of yours? that must be document table : create table document ( `id` int unsigned auto_increment, `title` varchar(128) not null, primary key(id) ); block table create table block ( `id` int unsigned auto_increment, `id_document` int // foreign key to document primary key(id) ); ...


1

if you are going count the tables in DB then query would be SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...


1

First, just to be certain we're covering our basis, this piece of code: EXP FILE = B.DMP OWNER(B) won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database: SQL> create ...


1

Grant EXP_FULL_DATABASE privilege to user A and then export. SQL> grant EXP_FULL_DATABASE to A;


1

http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2641 If you do not have the system privileges contained in the EXP_FULL_DATABASE role, then you cannot export objects contained in another user's schema.


1

There's no single right answer for any performance question. The answer is always that query optimizers are very smart, to a point. So the most efficient design or query will depend on the number of records, what kind of indexes you have, how selective they are, and many, many more factors. If you want to avoid keeping both bounds of your ranges, you can ...



Only top voted, non community-wiki answers of a minimum length are eligible