General Archives - Page 2 of 3 - Himanshu Patel SQL Server consultant Developer DBA Administration

Find current database physical file detail in SQL server

Database files information using query

Leave a Comment / General / By Himanshu Patel / July 25, 2020

database_files: Contains a row per file of a database as stored in the database itself. This is a per-database view Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-filegroups-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

Query statistics for a single table

Leave a Comment / General / By Himanshu Patel / July 25, 2020

sys.stats : Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in SQL Server. sys.dm_db_stats_properties: Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. Use the following query to identify statistics auto-created by SQL Server …

Query statistics for a single table Read More »

IO Statistics in SQL Server using Query

Leave a Comment / General / By Himanshu Patel / July 25, 2020

TSQL

Find all constraints that need to be entrusted.

Leave a Comment / General / By Himanshu Patel / July 18, 2020 / Constraint check

The last column of the result set is the SQL command to execute to entrust the constraint. Using cursor

Database model

Leave a Comment / General / By Himanshu / April 12, 2020 / conceptual data model, logical data model, three levels of data modeling

There are three levels of data modeling, conceptual data model, logical data model, and physical data mode. Feature Conceptual Logical Physical Entity Names ✓ ✓ Entity Relationships ✓ ✓ Attributes /Column ✓ Primary Keys ✓ ✓ Foreign Keys ✓ ✓ Table Names ✓ Column Names ✓ …

Database model Read More »

How to work with a collation

Leave a Comment / General / By Himanshu / April 12, 2020

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. Different options you see in the collation name. CS – case-sensitiveAI – accent-insensitiveKS – kana …

How to work with a collation Read More »

How to and where to use inbuilt objects in SQL server

Leave a Comment / General / By Himanshu / March 28, 2020 / How to use SQL server basic system tables and views

There are multiple options to get database table and index details. Here I explain some useful objects and their use. sp_help Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-ver15 sp_helpindex Reports information about the indexes on a table or view. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpindex-transact-sql?view=sql-server-ver15 sp_helpconstraint …

How to and where to use inbuilt objects in SQL server Read More »

How to get all database-level information using T-SQL

Leave a Comment / General / By Himanshu / March 28, 2020 / How to get all database-level information using T-SQL

How to get database information using system tables. Here is the sample with flowing fields: database_id : Database Id dbName : Database name Status : database state like 0 DataFiles : count of data files 1 = RESTORING 2 = RECOVERING | SQL Server 2008 and later 3 = RECOVERY_PENDING | SQL Server 2008 and …

How to get all database-level information using T-SQL Read More »