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 ...


18

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

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

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

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 ...


8

No. The sys.dm_db_index_usage_stat view reflects only, at best, data since the last database startup (last instance startup, or last time the DB was brought online). Furthermore the entries may clear under memory pressure. It will give accurate positives (if a table has non-zero stats then it is used)) but may give false negatives (0 usage in stats may not ...


7

There is also SQL Search - a free tool that integrates with SQL Server Management Studio.


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 ...


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: ...


6

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 ...


6

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 ...


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

How about: SELECT * INTO YourTableName FROM sys.database_files;


5

I've Community Wiki'd this Oracle Storage can hold multiple databases Databases can be opened by multiple instances Server can run run multiple instances A server can only run a single instace for any given database Data is stored in tablespaces A tablespace may be made up of multiple data files Tables/indexes/Materialized Views can be partitioned across ...


5

Found beta of a product that does this because I was asked the question directly. Not affiliated with this company is any way. See "How To Return Document Property Values in Full-Text Search" at: http://www.sqlskills.com/blogs/bobb/.


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 ...


5

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 ...


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 ...


4

SELECT column_name, data_type,character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' and TABLE_SCHEMA='yourDBname';


3

Doing that is not supported. You can try a little bit, and you will succeed a little bit, but everything else around you will fail. Don't do it, it won't work. If you need to protection your intellectual property from your clients, create a proper contract or license agreement.


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

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.


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

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 ...



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