36

You need to use this key combination: CTRL + SHIFT + R Alternatively, use the menu item: 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


36

Here's a simple query to match up foreign keys to their referenced tables/columns: SELECT o1.name AS FK_table, c1.name AS FK_column, fk.name AS FK_name, o2.name AS PK_table, c2.name AS PK_column, pk.name AS PK_name, fk.delete_referential_action_desc AS Delete_Action, fk.update_referential_action_desc AS Update_Action FROM sys....


31

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


29

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


22

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


19

Using information_schema This is the standards-compliant cross-RDBMS way to do it. SELECT table_catalog, table_schema, table_name, column_name FROM INFORMATION_SCHEMA.columns WHERE column_name = '<your column name>'; You can see this documented PostgreSQL SQL Server MySQL 8.0


19

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


17

This is based on the misconception that XML indexes are currently the only type that could ever have an id scheme that is >= 256000 (at least based on their observation; this scheme is not documented AFAIK, so not even sure if it's intentional). Probably fine in current versions, but who knows what type of index will be added next and where its id scheme ...


17

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


16

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1). CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL, column_that_we_will_drop TEXT ) ; -- Function that ...


15

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


15

Here's a better script. It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints. It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like ...


14

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


12

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


12

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these: SELECT tbl.name FROM sys.tables AS tbl LEFT JOIN sys.foreign_key_columns AS fKey ON tbl.object_id = fKey.parent_object_id LEFT JOIN sys.foreign_key_columns AS rKey ON tbl.object_id = rKey....


11

Within MySQL, you can get this information by creating a temporary table, then using DESCRIBE on that temporary table: CREATE TEMPORARY TABLE `temp` SELECT ... FROM ... LIMIT 0; DESCRIBE `temp`; We cannot just use DESCRIBE on the original table because the column we want to know the type of is a calculated column, not something being directly pulled from ...


11

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


10

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


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

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


10

To update comment, don't use alter command. Here is the PostgreSQL syntax: comment on column app_user_bookings.mod is '1# mobile booking, 2# admin booking, 3# web booking, 4# tell call' It makes sense to add a check to prevent wrong values appearing: ALTER TABLE app_user_bookings ADD CHECK (mod IN (1,2,3,4));


9

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables: USE tempdb; GO CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one CREATE TABLE dbo.a ( a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z) ); CREATE TABLE dbo.b ( b INT PRIMARY KEY, a INT FOREIGN KEY ...


9

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


9

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


9

Right from the manual: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is ...


9

Database- and server-level triggers are not scoped as "objects" per se (this is why you cannot create them under a schema, and why they don't show up in sys.objects). You can see that these objects have certain restrictions on them, for example in the OBJECTPROPERTY() docs: This function cannot be used for objects that are not schema-scoped, such as ...


8

This first query will get your foreign key constraints for your database, as well as the owning table and the referenced table: select name as FkName, object_name(parent_object_id) as ReferencingTable, object_name(referenced_object_id) as ReferencedTable from sys.foreign_keys If you want to drop a FK constraint, you'd do something like this: ...


8

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


8

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


8

Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query... This was reported by Adam Machanic on ...


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