Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [metadata]

Data that describes other data or data structures.

0
votes
1answer
38 views

How to create relationship metadata between tables without a foreign key check?

I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with ...
0
votes
1answer
23 views

DROP TABLE IF EXISTS hangs on Waiting for table metadata lock

I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows: I start up the main application. It opens up a connection to MySQL, but ...
1
vote
2answers
24 views

How to identify the ranges over which a postgres table was partitioned?

If I create a table and partitions like this... CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a); CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100); CREATE TABLE tab1_p2 ...
1
vote
2answers
56 views

SQL To Fetch Partition Boundaries and Filegroups

For a given partition scheme name, I need to retrieve the set of Boundary Value FileGroup Name This must work without the existence of any tables participating in the scheme. i.e. joins via sys....
0
votes
3answers
142 views

Where to find the OID of a newly created Schema

I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can ...
1
vote
1answer
35 views

How to select info about indexes with specified columns from all databases in the instance using TSQL?

This query selects all the indexes - its names, fragmentation, and some other parameters from the current database. select idx.[name] as [index_name], sc.[name] as [schema_name], obj.[...
1
vote
4answers
67 views

replace all references to a drive inside the jobs to another drive

I have a server where all my sql server backups are currently being saved to the G: drive. Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add ...
1
vote
3answers
54 views

How to get the definition of a database trigger?

when I run the following script in one of my databases: SELECT * FROM SYS.triggers when I try to find out about sys.triggers Contains a row for each object that is a trigger, with a type of TR ...
3
votes
1answer
72 views

How large is SQLites minimum data storage overhead and what generates them?

Motivation: In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
2
votes
1answer
133 views

Okay to run sp_refreshview against all views on a regular basis?

Microsoft SQL Server has a system stored procedure called sp_refreshview which updates the metadata of the view that's passed in. Are there any downsides to running this procedure regularly on all of ...
0
votes
1answer
36 views

How to join sys.columns and sys.synonyms table using object_id column [duplicate]

I have used two servers, Server A(local server) and Server B(remote server). I have created synonyms in the Server A for the Server B table. I have created synonym using following syntax. CREATE ...
2
votes
1answer
149 views

How to find Stored Procedures With Recompile?

In my databases I have some Stored procedure with recompile The way I find them is: SELECT OBJECT_NAME(ID)AS SP_NAME,* FROM SYSCOMMENTS WHERE TEXT LIKE '%WITH RECOMPILE%' when I look at: select * ...
0
votes
1answer
71 views

how to get ALL the objects in a database that are NOT FOR REPLICATION?

“Not for Replication” is a property which can be set for different objects like check constraints, Foreign Key constraints, Triggers , Identity columns etc while using SQL Server Replication. What ...
6
votes
2answers
255 views

Determine if user-defined type is ENUM

Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM? Essentially we have the following: CREATE TYPE foo AS ENUM ( 'Sometimes', 'You', 'Wanna', 'Go', '...
8
votes
1answer
244 views

How can I resolve a database trigger's name with built-in functions?

I have a database trigger that I use to prevent me from creating certain procedures in user databases. It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it....
1
vote
4answers
92 views

Check if two tables have the same structure (are compatible)

I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense ...
0
votes
0answers
24 views

How to hide metadata from Postgres users on Postgres 10.1 Amazon RDS

I have multiple clients and I have segregated them by creating separate schemas with views, that access the underlying data. However when they connect with tool like DBeaver or Pgadmin they are able ...
3
votes
2answers
158 views

Select orphaned rows (in a generic way)

I have a table my_tables which gets referenced as foreign key in several tables. I would like to select all rows of my_table which are not referenced in an other table. AFAIK, it should be possible ...
0
votes
1answer
18 views

Resolving Queries into a List of `object name.field`

I work on a team that has an SSRS setup with roughly ~1300 (and growing) canned reports. As one can imagine, this has presented problems when a breaking change is introduced to a table. Finding all ...
1
vote
1answer
228 views

Find tables with multiple indexes on same column [closed]

I am having an issue with scaffolding PostgreSQL DB because npgsql does not handle tables with two index on the same column well. Here's a link to my project on github. I am trying to find these ...
1
vote
2answers
133 views

SQL Server 2016 Temporal tables metadata

Is there any way to get metadata (information scheme) information about temporal table feature for the table, like: Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF What name of history table Is ...
4
votes
1answer
75 views

Retrieve All MySQL/MariaDB Table Definition From .frm Files

I wonder if it is possible to retrieve the MySQL/MariaDB table definitions from the .frm files? For example, if I wanted to steal your database and I had access to your MySQL/MariaDB files but didn't ...
1
vote
1answer
83 views

MySQL: Get number of rows of table with specific name for all databases

I am trying to build a mysql query (I want to use it in phpMyAdmin) to get the number of rows of all tables where the table name contains the word "user" or "member". But instead of questioning a ...
3
votes
2answers
298 views

Getting the exact count of rows in Postgres database

Is there any way to get the exact row count of all tables in Postgres in fast way? Count(*) seems to run very slowly for larger tables. Is there a way to get an exact count from Postgres statistics ...
2
votes
2answers
1k views

the object definition including the line break as sp_helptext does

object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently ...
7
votes
1answer
176 views

sys.objects column [Type] weird value 'ST'

I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table) Does anyone know what this 'ST' value means? (...
1
vote
0answers
107 views

Multiple many-to-many database relations

SO told me to ask here, so this is just copy-pasted from over there We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio ...
0
votes
2answers
71 views

How to increase performance with joins to create VIEW from Wordpress wp_usermeta [duplicate]

After a lot of searching, I finally was able to create a SQL Query to retrive Wordpress User Meta Data. Now the problem is, the performance is so bad it doesn't even show me a result after 30 min. ...
7
votes
1answer
151 views

Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016. With earlier ...
2
votes
1answer
192 views

Correct case for each reserved word in SQL Server? [closed]

Is the most correct spelling for int upper case INT or lower case int? There are many other reserved words in SQL Server. How to find out the best case for each word? For example the picture below ...
1
vote
1answer
131 views

Need an efficient routine to pass object name, with or without “[” and “]” brackets, to get ObjectID

Someone in their wisdom created a table called user: select schema_name(schema_id) ,* from sys.tables where type = 'U' and name like '%user%' select top 100 * from usr.user Msg 156, Level 15, ...
3
votes
1answer
157 views

Backing up Oracle Database Services

I'm currently working on project to clean database services on oracle database. Analyze first whats not needed and then drop. I need to prepare backup of services data for rollback purpose. (toad can ...
1
vote
0answers
540 views

Track my own custom metadata on Postgres tables and columns

Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text? I am migrating tables, columns, and data from another database system. That other ...
1
vote
2answers
695 views

List of values accepted by a column

Table Animal has following columns: SR_ID First_Name Last_Name PET The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH. Sample Data +-------+-------------+----...
1
vote
1answer
46 views

MySQL metadata search?

I'm coming from SQL Server background and getting into a project that uses MySQL. Is there any way to search the metadata of a MySQL database like there is with the Redgate SQL Search tool for SQL ...
1
vote
1answer
1k views

List all Oracle procedures/functions (including in packages)

I want to list all procedures/functions in an Oracle server, including procedures/functions in packages. Am I doing it right? SELECT CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE ...
1
vote
1answer
55 views

MySQL - Are column names stored in each row in full length?

MySQL: I'm not finding this in the manual or in google, but do column names get stored in full in each row? I suppose that this might change depending on compression, but leaving compression aside, ...
7
votes
1answer
4k views

How to add comment to a column in a table in Postgres?

I'm new to Postgres. I would like add a comment to my column mod in a table called app-user-bookings I have tried this sql code: alter table app_user_bookings modify column mod int default 1 ...
2
votes
1answer
52 views

How do I write a Postgres SQL command based on metadata of the tables themselves?

I want to retrieve all the records that were modified in the past hour of running the command. It is purely a meta data command (with no regard to the content of any tabular column data). I want to ...
1
vote
1answer
257 views

What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached ...
1
vote
2answers
4k views

Copy a view from one database to another database on the same server [closed]

I want to copy a view from one database to another database, on the same server. Both databases are structured exactly the same, except for about 8 views added in one.
3
votes
3answers
3k views

Find all tables without foreign keys in a database

In SQL Server, how do we find all tables which do not have either of: foreign key constraints references by other tables' foreign keys
14
votes
4answers
1k views

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method: Introduce 2 new columns as C and D2....
0
votes
1answer
68 views

LUN for Database / Instance

Is it possible to get the total LUN's for a instance - database and logs separate? If not, would adding up the individual results of the query below per database yield that result? SELECT ...
5
votes
3answers
4k views

How to script statistics in Sql Server? (using T-SQL)

there is a very similar question here: Create SQL Script for Statistics But that is not what I want. I want to get back exactly what I have put in. For example: CREATE STATISTICS [...
3
votes
1answer
1k views

How to check if a table exists in Informix

What would be the query to check that a table exists in Informix ?
2
votes
2answers
2k views

I looking for a solution to design database for property real estate

I looking for right method how can I design part of database schema for my real estate website I have table "proprety_real_estat" contain Country, city, town and type of property (screenshot 01) ...
4
votes
1answer
65 views

Where are filtered-index dependencies listed?

Given a table of the form... create table dbo.FilterIDXTest ( id int not null identity primary key clustered, _bigint bigint, _varchar10 varchar(10), _varchar20 varchar(20), ...
0
votes
2answers
1k views

Get all database, table and column names and column definitions

I need to check the data is identical between SQL Server. For that I need following details. Let me know if this is possible. All Database Names table Names column Names Column Max Length in a table ...
0
votes
1answer
202 views

Get the underlying foreign key constrains based upon a view

I have the following relations: CREATE TABLE cities ( city VARCHAR (80) PRIMARY KEY, LOCATION point ); CREATE TABLE weather ( city VARCHAR (80) REFERENCES cities (city), temp_lo INT, ...