Re: Using the catalogue
Date: 18 Nov 2005 14:25:31 -0800
Message-ID: <1132352731.660980.49690_at_z14g2000cwz.googlegroups.com>
> Please confirm that this query is impossible to express in SQL.
Actually, using SQL to write SQL is fairly common for one time activities such as DBA maintenance activities or as part of application development. It is not typically to perform this type of activity dynamically on a regular basis.
One example, is that instead of typing the names of the columns, the catalog is accessed and then the output is re-formated with a text processor.
Another example is for the common maintenance activity to update the information about the distribution of key values for use by the query optimizer. One may generate the statements based on the view "information_schema.tables", which is part of the ISO SQL standard. The SQL statement to generate the commands is shown below where the plus sign is operator overload for concatenating and the brackets are used in case the table name is a resererved word.
select 'update statistics [' + table_schema + '].[' + table_name + ']' from information_schema.tables
Many vendors provide methods to treat the resulting relation of a SQL statement as another SQL statement that should be run. An example is the stored procedure "sp_execresultset: from MS SQL Server, where one just passes the SQL statement:
exec dbo.sp_execresultset _at_cmd = 'select ''update statistics ['' + table_schema + ''].['' + table_name + '']'' from information_schema.tables where table_type = ''BASE TABLE'''
Regarding your example for (field name, value, relation), such that field name is in {Name, Designation}, field type is String, and field value = "King" .
Actually the field type is not string - that is the physical implimentation. The logical implimentation (somethimes called a domain and sometimes a data type) would be more on the order of PersonName.
If the same attribute exists in multiple relations, then I would say that the schema is not normalized and the physical implimenatation is being presented instead. Very common is to impliment physical partitioning of rows by creating seperate tables e.g. Active Employees as one table and InActive Employees as a different table. Remapping back to a logical schema can be done by creating a view that unions the two physical tables, at which point, only the view needs to be operated upon. At this point, you question regarding searching all relations becomes moot as there is only one relation to operate upon. Received on Fri Nov 18 2005 - 23:25:31 CET