Tag Info

Hot answers tagged

24

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


20

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

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


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


9

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


9

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


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

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


8

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things. EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more ...


7

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


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


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' /


6

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


6

select * from INFORMATION_SCHEMA.columns where column_name = '<your column name>'


5

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


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

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


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

Most probably there are three schemas (users) that contain that 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

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

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


4

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


4

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 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes ...


4

i wrote this sp to create automatically the schema with all things, pk, fk, partitions, constraints... IMPORTANT!! before exec create type TestTableType as table (ObjectID int) here the SP: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --*************************************************************************************************' -- ...


4

Each row in InnoDB (let assume COMPACT format) has these headers: Offsets - one or two byte per variable length type field. NULL flags - one bit per NULL-able field, aligned to a whole number of bytes. So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed. Then comes primary key ...



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