Data that describes other data or data structures.

learn more… | top users | synonyms

5
votes
4answers
54k views

How to write a query to find all tables in a db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like: select ...
0
votes
2answers
82 views

Search entire database

Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It ...
4
votes
1answer
3k views

Netezza: What schema contains all the system tables / views?

Is there a schema I can look in or a query that I can run to see all the Netezza system or metadata tables? I know about _v_table, _v_view, _v_relation_column, and _v_groupusers. I know there are ...
0
votes
0answers
20 views

Modeling question - Meta and instance

In a relational DB I'd like to model this: every customer_type has one module_set (1:1) a module_set has several modules (1:n) every module is different, and the user can activate them & reorder ...
0
votes
0answers
23 views

Specify what columns to display in a SHOW COLUMNS statement

Does MySQL offer any way to specify which columns to display in a SHOW COLUMNS FROM my_table statement? From this : Field Type Null Key Default Extra ------ ...
5
votes
5answers
8k views

Field types for columns in a MySQL query

Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? edit: i should clarify that a bit. I am aware that I can get the ...
0
votes
1answer
45 views

Deny user to see any database through DB_NAME()

I've already prevented user to see any database (deny view any database to [login]) and it works. But how to prevent user to list any other database using DB_NAME()?
10
votes
2answers
116 views

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see ...
9
votes
4answers
48k views

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the ...
1
vote
2answers
34 views

How to return all the unique column values on a per column basis for all metadata tables?

assume multiple tables of various sizes table one col one, col two, col three,... a,d,j a,e,k a,e,m b,e,o c,e,o d,e,o f,e,o return (preferably in this format) col one, col two, col three a,d,j ...
10
votes
2answers
401 views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
2
votes
1answer
50 views

What exactly is metadata? How is metadata different than regular data?

As I understand, metadata is data about data- so I think of analytics; is my simple definition correct? How is metadata different than data? I'm simply asking to broaden my understanding-thanks!
4
votes
1answer
457 views

Find all tables that are related to a view recursively sql server

I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C). When I use ...
6
votes
2answers
261 views

Add [Is Primary Key Column] to this query

I made this query, to list tables, its columns, data type and etc: select t.name as 'Table', c.name 'Column', ty.name 'Data_Type', c.max_length as 'Max_Lenght', ...
5
votes
1answer
201 views

dm_exec_describe_first_result_set_for_object for multiple result sets

Does a procedure exist to return metadata for all result sets in a stored procedure? Something like sys.dm_exec_describe_first_result_set_for_object, but for all result sets? Eventually, I will want ...
5
votes
2answers
2k views

Is Information in sys.dm_db_index_usage_stats reliable

I'm archiving data from an old system that has no documentation. Lucky me... I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct ...
2
votes
1answer
55 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
4
votes
1answer
67 views

How to extract the structure out of DB2 (Back up Structure)?

Is there a way in db2 to take a backup of the database structure (Metadata) without actual data? My purpose is to pull structure (tables, views, indexes, triggers, procedures and functions) from an ...
4
votes
2answers
88 views

Where/how do I save extra data per column?

Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within ...
20
votes
4answers
944 views

What's up with the collation of some columns in sys.databases?

I'm attempting to run an UNPIVOT on various columns contained in sys.databases across various versions of SQL Server, ranging from 2005 to 2012. The UNPIVOT is failing with the following error ...
3
votes
0answers
30 views

ObjID in Page Header

I am reading this article: Inside the Storage Engine: Anatomy of a page. I have a database MyDB, and there is a table MyTable in the database. I have the following questions: If I do as follows: ...
6
votes
2answers
178 views

sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas. In my database I have a simple table like this: CREATE TABLE dbo.Invoices ( CashDiscounts MONEY, Cost MONEY ) GO and a stored ...
1
vote
0answers
28 views

Table metadata auto_increment value for table modification

if table X is modified(DELETE, INSERT, UPDATE) then table X's metadata value should get incremented. I can write trigger for the same. But inbuilt solution will be better. This way I can stop ...
4
votes
4answers
27k views

User Password Expiry date is null in user_users?

Is it possible to find my password expiry date without asking the DBA? My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is ...
0
votes
0answers
38 views

Is there, currently a practice, science, discipline that approaches the analysis of the meta-data of an organization?

I am trying to catalog the data in our organization. I just realized that once that data is cataloged -- essentially meta-data -- we can run analytics on the data itself. What I have in mind Let us ...
2
votes
2answers
224 views

How do I get a list of all the partitioned tables and partitioning details in my Teradata?

How do I get a list of all the partitioned tables in teradata? and How do I get a partitioning columns and details for a table in teradata ?
1
vote
1answer
114 views

Querying sys.master_files over a linked server

Querying sys.master_files through a linked server, I get no results. Why? How do I work around this issue?
2
votes
0answers
18 views

Persisting typed variables

Is it possible to associate typed variables with an object, such as a database, table, or row? Suppose I want to store the schema version as a NUMERIC.
2
votes
1answer
119 views

PostgreSQL - Who or what is the “PUBLIC” role?

I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist. => Who or what is ...
27
votes
4answers
73k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
8
votes
1answer
2k views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
3
votes
0answers
47 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
2
votes
3answers
259 views

Looking for industry standard and/or tool to represent and export RDBMS metadata [closed]

Do you know of an industry standard that is designed to describe the metadata contents of a relational database (or part of it, such as tables, columns, etc.)? Do you know if a tool exists that does ...
0
votes
0answers
94 views

Cleaning up orphaned metadata in information_schema

OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so: alter table mytable add constraint fk_mytable_myothertable foreign key (myothertable_id) ...
15
votes
5answers
6k views

Best way to store units in database

I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the ...
6
votes
4answers
2k views

How to document Oracle database?

I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the ...
0
votes
1answer
80 views

EAV table question

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text ...
1
vote
1answer
334 views

How to access the text definition of sql indexes

I am able to access the text definition of stored procedures, views, triggers and functions from sys.sql_modules. I want to get the text definition of indexes. How can I get the text definition of ...
1
vote
2answers
93 views

InnoDB row metadata size (minimum row size)

I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6). This is not exactly my case, but for simplicity, let's ...
3
votes
2answers
215 views

Is EAV an appropriate design for a collection of coexisting metadata-rich “fact estimates” on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
0
votes
0answers
57 views

Metadata database on top of flat files

I have a bunch (millions) of flat files. I do sort them into subdirectories, but that doesn't really help searches. Is there some database software that could be fed metadata information and would ...
0
votes
0answers
235 views

Getting Waiting for table metadata lock for select statements

I am using MySQL server 5.6.23 on CentOS and server configuration is like RAM - 32 GB Cores - 6 cores Innodb buffer pool size = 8 GB Key cache size = 2 GB We are using only select queries. But all ...
5
votes
3answers
15k views

Understanding Oracle's ALL_TAB_COLUMNS

I'm new to Oracle and database administration in general. As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE. I cannot find any specific ...
3
votes
1answer
253 views

Metadata Headache

A process that I don't have control over is dropping and re-creating tables in a MySQL database every night. This wouldn't be a problem (I think), if it re-created the tables identically every time. ...
1
vote
3answers
1k views

How to see percentage of existing values for all columns in mysql table?

I have three large-ish tables (~20 columns each) and I want to know what percentage of rows have a value for each column. For instance, this table: ID | TITLE | SERVING | NOTE ...
3
votes
1answer
231 views

How to format documentation in MS_Description extendedproperties?

I'm using a 'MS_Description' extended property to document parameters in a stored procedure. This parameter's documentation requires more structure than just plain text. That is I would like to ...
0
votes
1answer
37 views

Upgrade MongoDB meta-data after binary upgrade

I upgraded our MongoDB binaries from 2.2.5 to 2.4.9. I did not follow one of the considerations prior to upgrading: To upgrade to 2.4, sharded clusters must upgrade following the meta-data upgrade ...
0
votes
1answer
56 views

Photo caption and keyword associations

I am creating a database that references images. Keywords and captions will be part of the metadata. These captions and keywords are not necessarily added to the photos with the intent of facilitating ...
1
vote
1answer
104 views

SSMS Intellisense - Data Dictionary Incorporation - Show Object Descriptions in Popup? [closed]

Can anyone point me in the right direction for an SSMS intellisense replacement that will show the object descriptions in the popup? This will help speed up onboarding as well as making myself ...
0
votes
2answers
5k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...