Tag Info

Hot answers tagged

20

You can use following query to list all columns or search columns across tables in a database. USE AdventureWorks GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%EmployeeID%' ORDER BY schema_name, table_name; You ...


17

You need to use this key combination: CTRL + SHIFT + R Alternatively, use the menu Edit > IntelliSense > Refresh Local Cache. This article might also be useful (for future readers who have more perplexing IntelliSense issues): Troubleshooting IntelliSense in SQL Server Management Studio


10

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough. SELECT keyword, display_term, column_id, document_id, property_id FROM sys.dm_fts_index_keywords_by_property ( DB_ID('FileTableDB'), OBJECT_ID('FileTableTb') ); MSDN on ...


9

The easiest way to think of it is: DBA_ / USER_ / ALL_ views are built on the data dictionary - they're not available if the database is not mounted and opened. V$ views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view. Using your example: ...


8

In Oracle, you would use the COMMENT command to: [...] add to the data dictionary a comment about a table or table column, view, materialized view, operator, indextype, mining model, or edition. Most tools (PL/SQL Developer, Toad...) will display these comments in appropriate fields when you browse the database schema. The comments can be queried ...


8

Way late to the party, but just thought I'd mention a metadata enhancement in SQL Server Denali that will make it much easier - not only to inspect the output of a query without running it (not quite the same behavior as SET FMTONLY ON, which many apps use today), but also to build target tables dynamically (without all the parsing and case work involved ...


8

The object_id column is unique per database. Two objects in separate databases can have the same object_id, however separate objects in the same database have always different object_id values. Every time you drop and create an object, a new object_id value is assigned automatically and there is no way to influence which value is chosen. However, if you ...


7

Methinks a better query is as follows: select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object], i.name as [index], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id This looks at the 'proper' place to identify the partition scheme: ...


7

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole ...


6

In Oracle there are table and column comments that can be used for documentation. These comments can easily be added by the following commands: COMMENT ON TABLE my_table IS 'Documentation of my table' / COMMENT ON COLUMN my_table.my_columns IS 'Documentation of my column' /


5

You can script a table relatively easy using the UI of course: This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist). But if you're trying to automate this (e.g. generate the create table script in code), it is a little more ...


5

This query should give you what you want: select distinct t.name from sys.partitions p inner join sys.tables t on p.object_id = t.object_id where p.partition_number <> 1 The sys.partitions catalog view gives a list of all partitions for tables and most indexes. Just JOIN that with sys.tables to get the tables. All tables have at least one ...


5

Most DBMS already do store metadata in the database. This data is general stored in what is broadly referred to as "system tables". The metadata that a DBMS will store already will be what it needs to operate the database. Some of the types of metadata you've mentioned, e.g. ownership, permissions, and possibly even formats and descriptive names are ...


4

To my knowledge there are these 2 ways you can go: If you want performance, simpler queries, easier programming, then you should make a second table with your ID as foreign key and make there a column for each of your properties that you want to store. For any new attribute you would have to change the database schema, what might be a major drawback. The ...


4

All work. Note that in the second case, you cannot add apples and oranges, and so the data is exceptionally easy to be subject to misinterpretation. Also note that conversions cannot be very safe and are susceptible to rounding error, overflows, etc. In addition, there are physical issues like the specific gravity and temperature. Converting 20 gallons ...


4

Most probably there are three schemas (users) that have contain table. You need to include OWNER = 'FOOBAR' in your query to all_tab_columns (or use user_tab_columns). You can also include the OWNER column in your select list to verify this. For VARCHAR (and other character columns) the size is stored in CHAR_LENGTH as documented in the manual. Note that ...


3

The fact that pg_typeof doesn't show the typmod is frustrating. To get the fully qualified type you can query the system catalogs. Let's look at how psql does it using psql -E: $ psql -E regress psql (9.2.1) Type "help" for help. regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) ); CREATE TABLE regress=> \d typmodtest ... tons of ...


3

There isn't a comparable way to hand a query to MySQL and ask it to return a resultset containing the names and attributes of the columns that your query will return when it is executed. However, the library you're using to access MySQL probably has such a mechanism that your code could use... because on the wire, MySQL does return this information to ...


3

"Metadata visibility" determines what objects a user can see. Basically, their own objects (login, users) or what they have permissions on (tables, code etc). You can't hide an object that they have select/execute premission on. Simple. What you can do if to use schemas to create object groupings in SSMS to "declutter" John and Sarah's view. However, this ...


3

Sounds like you need to employ some database schemas to separate out user objects: http://msdn.microsoft.com/en-us/library/ms190387.aspx A database schema is nothing more than a collection of objects within a database. DBO and SYS are common built-in schemas in SQL Server. You can create a schema that houses a user's objects (tables, functions, stored ...


3

Late one but hopefully useful since both tools are free. ApexSQL Search – good thing about this tool is that it can also search data, show dependencies between objects and couple other useful things. SSMS Toolpack – free for all versions except SQL 2012. A lot of great options that are not related only to searching such as snippets, various ...


3

I would seriously advise against this design. Its a database anti-pattern called the Entity Attribute Value pattern. Here are some posts as to why: http://karwin.blogspot.com/2009/05/eav-fail.html http://www.jonathanlevin.co.uk/2008/04/sql-is-in-fact-programming-language.html A better approach would be to start with the columns/attributes you have and ...


3

MIKE? What is MIKE2.0? MIKE2.0 which stands for Method for an Integrated Knowledge Environment, is an open source methodology for Enterprise Information Management that provides a framework for information development . The MIKE2.0 Methodology is part of the overall Open Methodology Framework.


2

I'm not aware of any open-source tool that would scale to 'hundreds of enterprise databases.' The closest things that come to mind are: Dia has a basic modelling engine that could be used to create UML models. However, I think you would have to write a lot of glue to get the models into Dia in the first place. Eclipse Modelling Framework (EMF) has ...


2

For SQL Server, the Sybase poster will largely apply. The Oracle Interactive Quick Reference may also help.


2

Since PostgreSQL 9.2, there is also collation for (any) to get the collation (if any) - which is one of the 6 properties you can retrieve with SQL_VARIANT_PROPERTY in SQL Server. Works universally, not just for table columns. You'll find more on format_type() at the same page of the manual. @Craig demonstrated how to use it.


2

ALL_TAB_COLUMNS is showing you all of the tables you have access to, not just the ones you own. I would guess that there are three schemas on the server with your application database present. USER_TAB_COLUMNS would show you just the tables you own (i.e. just the ones in the schema belonging to the user you are logged on as). If you connect as a DBA login ...


2

If you want a set of views that are "common" across database vendors (Oracle, MySQL, PostgreSQL, ...), there are the INFORMATION_SCHEMA views. They are supposed to be a common standard across vendors (exactly how true I don't know). In SQL Server (versions >= 2000) you access them via [INFORMATION_SCHEMA].{view_name}, where {view_name} is any one of: ...


2

It might be that your account has been assigned the profile with PASSWORD_LIFE_TIME set to UNLIMITED, and thus your password will never expire. You can query the USER_PASSWORD_LIMITS view to determine what password limits are currently in effect for your account: SQL> select * from user_password_limits; RESOURCE_NAME LIMIT ...


2

The columns you are talking about, occupy 20 bytes (if aligned without padding): creation time, update time and creation source timestamp .. 8 bytes timestamp .. 8 bytes integer .. 4 bytes The tuple header and index pointer for a separate row in a separate table alone would occupy 28 bytes plus the 20 bytes of actual data. Read more here: ...



Only top voted, non community-wiki answers of a minimum length are eligible