From below query you will get the number of data present in each table of databases. Most consume table data.
–created the temp table to store the tables data size
CREATE TABLE #DBEachTable (
TableName sysname ,
–insert the tables data in created temp table
EXEC sp_msforeachtable ‘sp_spaceused ”?”’
–fetching the each individual tables data size
COUNT(*) AS ColumnCount,
FROM #DBEachTable a
INNER JOIN information_schema.columns b
ON a.TableName collate database_default
= b.table_name collate database_default
GROUP BY a.TableName, a.RowCounts, a.DataSize
ORDER BY CAST(REPLACE(a.DataSize, ‘ KB’, ”) AS integer) DESC
In below query, We can view the relationships between more than one table as primary and foreign key …
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
This will help you to identify the relationships between the two tables..
In below sections there are basics sql queries about SELECT , WHERE , AND OR NOT , INSERT … There are other query commands also those we will see in the Next blog …
Select command is use for to get the data from the table
Example: SELECT * FROM tablename
The above example will display all the columns from the table. If we want to display particular fields in the table then we have to use column-name operator.
If we want to display certain field without any duplicates then we use the DISTINCT keyword along with the select command.
If we need only certain records from the table then we use the where clause. Where clause acts as a Filtering mechanism. Under the Where section we need to specify certain conditions, only if those conditions are met the records will be extracted.
Example: SELECT * FROM tablename WHERE columnname = value
AND, OR, NOT
If we need to add two or more conditions in the where clause then we can use the above-mentioned operators. These keywords will add more complexity to the query.
- AND Operator: This operator displays a record if all the conditions separated by AND are TRUE.
- OR Operator: This operator displays a record if any of the conditions separated by OR is TRUE.
- NOT Operator: This operator displays a record if the condition/conditions are NOT TRUE.
If we want to insert any new record or data into a table then we can use the INSERT query. We can use the Insert into in two ways:
Here we specify the column names for which we need to insert the record.
INSERT INTO table_name (column1, column2,…)VALUES (value1, value2, value3, …);
In a SQL Server there is two things Blocking and Locking. Today will check about blocking.
There are number of ways to find out the details of the system processes IDs (spids) involved in blocking. I have tried to cover some of the options in this tip to include:
- sp_who2 System Stored Procedure
The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc.
For Checking in SQL use below Query.
The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.
WHERE blocking_session_id <> 0;
The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
WHERE blocking_session_id <> 0
Whenever SQL Server needs to perform a query execution additional following Transact-SQL statements are executed:
3) GROUP BY
6) ORDER BY
We will be give more details about above conditional clauses in next blog…
Index scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
A scan is the opposite of a seek.
Index seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index seek uses the index to pinpoint the records that are needed to satisfy the query.
Be yourself; Everyone else is already taken. — Oscar Wilde.
This is the first post on my new blog. I’m just getting this new blog going, so stay tuned for more. Subscribe below to get notified when I post new updates.
Hi every one! My name is Nitin, I live in Mumbai, and a database developer for almost 5+ years (SQL).
I started using WordPress for a SQL blog sql.tech.blog … So that was my real first approach with WordPress as a user. I started to have an interest as a developer.