Get in-memory OLTP index usage
sys.dm_db_xtp_index_stats (Transact-SQL) – SQL Server | Microsoft Docs
Table Info
Foreign Key scripts
We need to have ready scripts when we need to clean all data with reseed identities. Please make changes in this as per your requirements. Drop temp table Create temp table with Foreign Key (FK) values Generate foreign key drop scripts from temp table Generate foreign key scripts from temp table. Other References:
Tables with more than 30 columns (wide tables)
This query returns list of tables which contains more than 30 columns. Other References:
Re-assign the identity of all the tables.
If the alignment of Identity matters to your business logic then you go with this script. However, keep in mind the cases when you may have the Lost Identity especially when there is often switching in the clustered /replication environments. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-identity-columns-transact-sql?view=sql-server-ver15
Find SQL Server collation
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type. Case sensitivity If A and a, B and b, etc. are treated in the same …
Tables with Identity columns
How to find table which use identity column in SQL Server using TSQL. To list all identity column To list all identity column with table name, last value, seeding value incremental value, replication stetting, etc.
Tables with at least one LOB (max) column
Returns a table list which have maximum length of column.
Get fragmentation info for the tables
dm_db_index_physical_stats:Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15
Tables with at least one TEXT, NTEXT, IMAGE column
Returns list of tables from database which have text, ntext, image columns
Tables with more than 5 indexes
This query returns over index table list Other Refrences:
