I'm trying to test if a given default constraint exists. I don't want to use the sysbojects table, but the more standard INFORMATION_SCHEMA. I've used this to check for tables and primary key constraints before, but I don't see default constraints anywhere. Are they not there? (I'm using MS SQL Server 2000). EDIT: I'm looking to get b...
I need a smart way to get the data types out of INFORMATION_SCHEMA.COLUMNS in a way that could be used in a CREATE TABLE statement. The problem is the 'extra' fields that need to be understood, such as NUMERIC_PRECISION and NUMERIC_SCALE. Obviously, I can ignore the columns for INTEGER (precision of 10 and scale of 0), but there are ot...
I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another. SELECT t1.Routine_Definition FROM [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 LEFT JOIN [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name WHE...
I'm looking for the most portable method to check for existance of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008. The information does not appear to be in INFORMATION_SCHEMA, but if it is in there somewhere, I would prefer to use it from there. I do know of this method: if exists ( ...
What is the recommended way to figure out if a user got a certain right (e.g. select or execute) on a certain class (e.g. table or function) in PostgreSQL? At the moment I got something like aclcontains( someColumnWithAclitemArray, makeaclitem(userOid,grantorOid,someRight,false)) but it's terrible since I have to check for ev...
Using Oracle, how can I find index names and creation dates from systables/information_schema? How can I reproduce, from systables/information_schema, the DDL that created the index, e.g., create index indexname on tablename(column_name [, column_name....]) [local]; ...
I am using Mysql NDB Cluster, in my information_schema.Tables tbl I have null entries in the colummn called Create_Time and Update_Time. Does anyone have any known solution for this. as on my normal innodb databases the Create_Time and Update_Time is there properly. ...
I know that so far (until MSSQL 2005 at least), system databases are master, model, msdb and tempdb. Thing is, as far as I can tell, this is not guaranteed to be preserved in the future. And neither the sys.databases view nor the sys.sysdatabases view tell me if a database is considered as a system database. Is there someplace where t...
So here is the problem.. Using TSQL and INFORMATION_SCHEMA or sys views, how can I identify a 1:0-1 relationship, such as FK_BaseTable_InheritedTable? In a concrete example, imagine a simple DTO - FK_JoinTable_ParentTable would be rendered as a collection of JoinTable on the ParentTable object, whereas FK_BaseTable_InheritedTable woul...
How do a I list all tables containing a given column name? I'm using Mysql version 4.1.13-nt-log. I know versions less than 5 dont have an information_scheme DB. ...
Hello I need to get schema details for the passed table name using information_schema. As part of output need ColumnName, Constraint_Name, Constraint_Type. How can this be done Thanks ...
What is the best way to list all of the tables within PostgreSQL's information_schema? To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table in the information_schema structure. ...
Hi, I am working on an assignment where I am supposed to find all of the tables in information_schema of a "blank" database. I can do this in either MySQL or PostgreSQL. I chose MySQL. So, I have identified all of the tables: CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS COLUMN_PRIVILEGES INDEX_STATISTICS KEY...
This is going to sound like a crazy request. The databases that I report from do not have any foreign keys, and every single primary key is an identity_column. This makes using tools such as TOAD difficult because the Intellisense works by reading the PK and FK relationships. Anyone have a script to remove the primary keys from every t...
I have this sql: ALTER TABLE dbo.ChannelPlayerSkins DROP CONSTRAINT FK_ChannelPlayerSkins_Channels but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name FK_ChannelPlayerSkins_Channels. ...
Given: I added a non-nullable foreign key to a table. I settled on a way to populate the foreign key with default values. I checked in both changes to a re-runnable DB creation script. Other developers ran this script, and now they have the foreign key populated with default values on their developer machines. A few days later howeve...
For a specific database in a MySQL Server 5.5 I would like to view all grants given to any user. To do this I have been reading from the information_schema.schema_privileges table using a select statement as follows: select * from information_schema.schema_privileges where table_schema='myproject'; The problem is that I only see my ow...
I have been given access to a SQL Server database that is currently used by 3rd party app. As such, I don't have any documentation on how that application stores the data or how it retrieves it. I can figure a few things out based on the names of various tables and the parameters that the user-defined functions takes and returns, but I...
hi what i am trying to do is get all the collums of a table (the table can be empty) example of what i did before: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'aTable' AND table_schema = 'theDatabase' it works perfectly on localhost however on my provider it gives: #1142 - SELECT command denied to user ...
I want to run a diagnostic report on our SQL Server 2008 database server. I am looping through all of the databases, and then for each database, I want to look at each table. But, when I go to look at each table (with tbl_cursor), it always picks up the tables in the database 'master'. I think it's because of my tbl_cursor selection : ...