Overview of Synonyms in MS SQL Server

Hi Friends,

Hope you are doing fine, while reading to this post!

Today, I will try to write about the overview of SYNONYMS in MS SQL Server.

Synonyms are the nothing but the database object, which is used to specify/provide an alternative name to other database objects. We can use synonyms so that user does not need to remember the full name of the database object, as somewhere we have seen the complexity within the object's name.

For more details, please refer the MSDN Link .

Going forward, Synonyms can be used for the database objects mentioned below:

1. User-defined Tables
2. User-defined Views
3. User-defined Stored Procedures
4. User-defined Functions

Also please note the below points about the SYNONYMS:

1. A synonym can not refer to another synonym.
2. You can not be alter SYNONYM. To modify the synonym you must DROP it and recreate.
3. Synonyms names must be T-SQL identifiers just as for other database objects.

CREATE SYNONYM:

To create a synonym, we simply have to specify a synonym name and name of database object to which it will be assigned.

Syntax#

CREATE SYNONYM <[schemaName].[synonymName]> FOR <[SchemaName].[TableName]>

DROP SYNONYM:

Since, we can not ALTER the synonym in MS SQL Server, we need to drop and re-create it, if we wish to change the SYNONYM.

Syntax#

DROP SYNONYM <[schemaName].[synonymName]>

Let's understand this with below T-SQL Script:

-- SELECT DATABASE 
USE AdventureWorks2012
GO

-- CREATE SYNONYM FOR THE TABLE 
--  '[Production].[ProductModelProductDescriptionCulture]'
CREATE SYNONYM [dbo].[ProdDescCulture] 
    FOR [Production].[ProductModelProductDescriptionCulture]
GO

-- SELECT QUERY ON THE ORIGINAL TABLE 
-- '[Production].[ProductModelProductDescriptionCulture]' 
--(Extracting only TOP 2 Rows)
SELECT TOP 2 * FROM [Production].[ProductModelProductDescriptionCulture]
GO

-- SELECT QUERY ON THE ORIGINAL TABLE 
--'[Production].[ProductModelProductDescriptionCulture]' 
--(Extracting only TOP 2 Rows)
SELECT TOP 2 * FROM [dbo].[ProdDescCulture]
GO

-- DROP SYNONYM QUERY
DROP SYNONYM [dbo].[ProdDescCulture]
GO

Snap-shot:

Hope you will like this post!

Happy Coding :-)

T-SQL query to get database size and table size (in GBs)

Hi Friends,

Hope, you are doing good...!

Please find below T-SQL query to find DB size and Tables Size (In GB) 
along with their row count. 

T-SQL query to get Database size in GB:
SELECT 
  dbs.NAME, 
  CAST((SUM(mFiles.SIZE) * 8 / 1024.00 / 1024.00) AS NUMERIC(18,2)) 
      AS [DB SIZE (In GB)]
FROM
   SYS.MASTER_FILES mFiles INNER JOIN SYS.DATABASES dbs
      ON dbs.DATABASE_ID = mFiles.DATABASE_ID
WHERE dbs.DATABASE_ID > 4 
             -- FILTER OUT THE DATABSES AS "master", 
             -- "tempdb", "model" AND "msdb"
GROUP BY dbs.NAME
ORDER BY [DB SIZE (In GB)]

T-SQL query to get tables size in GB with no of rows:

USE <Database_Name_To_Be_Used>
GO
SELECT 
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 
 AS [Table's Total Space In GB]
FROM 
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC

Original post is published here at my primary blog:
https://gauravlal.wordpress.com/2013/07/12/t-sql-query-to-get-database-size-and-table-size-in-gigabytes/
Please share your feedback if you like this post!!!





Happy Coding :-):-) 

T-SQL to find the list of all Check Constraints associated with the objects within a particular database

Hi Folks,

Hope you are doing fine!

Today, I will share a simple T-SQL script to find the list of CHECK CONSTRAINTS defined and associated with the database objects within a particular database. Please find below the T-SQl script for your reference:

T-SQL Script:

USE [Northwind]
GO
/*   T-SQL to find the list of all CHECK CONSTRAINTS 
        associated with the objects within a particular database...   */
SELECT 
 o.type_desc AS ObjectType,
 '[' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' 
            AS ObjectName,
 ISNULL(clmn.name, '') AS ColumnName,
 '[' + SCHEMA_NAME(c.schema_id) + '].[' + c.name + ']' 
            AS CheckConstraintName,
 c.definition AS CheckCondition,
 c.is_system_named AS IsSystemDefinedName,
 c.is_not_trusted AS IsNotTrustedCheckConstraint,
 c.is_disabled AS IsDisabledCheckConstraint,
 c.create_date,
 c.modify_date
FROM 
 sys.check_constraints AS c
 INNER JOIN sys.objects AS o ON c.parent_object_id = o.object_id
 LEFT JOIN sys.columns AS clmn ON c.parent_object_id = clmn.object_id 
              AND c.parent_column_id = clmn.column_id
ORDER BY 
 ObjectName, CheckConstraintName, ColumnName
GO

Snap-shot:

Originally posted here at my primary WordPress blog!

Hope, this may help you somewhere...!

Happy Coding :-)