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

Data that describes other data or data structures.

0
votes
0answers
19 views

SSRS Report Metadata Tables to help ease pains from constant change

Situation: I work in a position doing database development and there is a catalog of about 300 SSRS Reports the business uses. These reports are constantly needing to be updated to help the business ...
0
votes
3answers
63 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
63 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
43 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
60 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
94 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
24 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
118 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
60 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
239 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
243 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
75 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
21 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
142 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
190 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
117 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
58 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
48 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
261 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
156 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
100 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
61 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
145 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
173 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
129 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
150 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
473 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
516 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
43 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, ...
5
votes
1answer
3k 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
50 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
234 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
3k 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
60 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
938 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
170 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, ...
2
votes
1answer
4k views

How to get Page Name given Page ID in Oracle Application Express

Prefix: I have looked all over the net for this answer. Let me prefix the question by describing what I am trying to do: I am trying to create a unique list of shortcuts to pages in my application for ...
0
votes
1answer
440 views

How can I (re-)create the Entity Relationship Diagram (ERD) of an existing hidden Oracle database?

More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing hidden Oracle Database. In my ...
0
votes
1answer
96 views

where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA

Quote from MariaDB 10.2 CHECK and DEFAULT clauses: Another limitation is that CHECK metadata are not accessible via the INFORMATION_SCHEMA. The only way to find out if a table has CHECK clauses ...