Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [metadata]

Data that describes other data or data structures.

184 questions
487
votes
5 answers

List all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and…
Stephane Rolland
  • 7,903
  • 8
  • 29
  • 40
44
votes
5 answers

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 for a quick way to do this. What do you recommend?
Webber
  • 655
  • 1
  • 6
  • 8
35
votes
7 answers

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 table; all of the indexes / constraints are…
jcho360
  • 1,979
  • 7
  • 23
  • 31
28
votes
4 answers

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
RK Kuppala
  • 2,407
  • 1
  • 21
  • 24
26
votes
5 answers

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 MS_Description field of Extended Properties. I am…
kmote
  • 607
  • 1
  • 6
  • 13
26
votes
1 answer

How can I force SQL Server Management Studio to update the IntelliSense cache?

IntelliSense in SSMS isn't updating unless I shut it down and restart. I've tried disconnecting from the server and reconnecting while the SSMS session is active, but that isn't working. Steps to recreate: Create a table Select something from…
Head of Catering
  • 629
  • 1
  • 10
  • 24
21
votes
4 answers

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 message: Msg 8167, Level 16, State 1, Line 48 The type…
Hannah Vernon
  • 68,431
  • 22
  • 166
  • 303
20
votes
2 answers

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 the public role?
gokan
  • 303
  • 1
  • 2
  • 4
19
votes
2 answers

Find the Foreign Keys Associated with a Given Primary Key

I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu WHERE EXISTS ( SELECT tc.* …
MoonKnight
  • 357
  • 1
  • 4
  • 12
15
votes
5 answers

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I…
Jeremy Holovacs
  • 1,152
  • 4
  • 16
  • 25
15
votes
1 answer

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 found a few different solutions: Store the meta…
Tiddo
  • 997
  • 2
  • 8
  • 11
15
votes
4 answers

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. Copy the contents of D to D2. Delete D. Rename…
Andy
  • 573
  • 3
  • 12
14
votes
3 answers

Is there an Open Source Metadata Management Solution?

Is there an Open Source Metadata Management Solution? I'd like to create a Metadata Repository that will hold the details of the metadata of database schemas, tables and data items of hundreds of Enterprise databases. I'm especially interested in…
Stuart Woodward
  • 488
  • 2
  • 6
  • 14
14
votes
8 answers

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 from where exists table.column name; How…
Joe Essey
  • 351
  • 1
  • 6
  • 11
13
votes
2 answers

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 below) has served me well until we began creating…
swasheck
  • 10,555
  • 4
  • 45
  • 88
1
2 3
…
12 13