Hot answers tagged

29

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


24

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


16

The official word from Microsoft: Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata (not user metadata) and basically these strings are ...


15

                          Please note that the following info is not intended to be a comprehensive description of how data pages are laid out, such that one can calculate the number of bytes used per any set of rows, as that is very complicated. Data is not the only thing taking up space on ...


13

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


13

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


11

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


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


10

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


10

Query that adds "is_primary_key" field for each column The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK. Also, you want to use [user_type_id] ...


9

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


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

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

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


8

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


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

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

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


7

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


7

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


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

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


6

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


6

Maybe this is a grunge answer but this is what I would do. So DATALENGTH only account for 86% of the total. It is still very representative split. The overhead in the excellent answer from srutzky should have a pretty even split. I would use your second query (pages) for the total. And use the first (datalength) for allocating the split. Many ...


6

Background on Collation Precedence The behavior you are seeing with regards to the Collation of various fields in the system catalog views is a result of how each field is defined and Collation Precedence. When looking at sys.databases, it is important to keep in mind that it is not a table. While in the past (I think ending at SQL Server 2000) these were ...


5

This approach works fine for me: SELECT * FROM [linked_server_name].master.sys.master_files; What you need to be sure of is that the linked server is running in the context of a user who has enough privileges to see the contents of the DMV. You can check who you're executing as using: EXEC [linked server name].master.sys.sp_executesql N'SELECT ...


5

This is a bug in SQL Server and here is a Connect item to vote for if you want a change. dm_sql_referenced_entities does not shows columnes when temporary tables are used in statement Current status: We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.


5

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is ...


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

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



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