How to specify a Port Number to Connect to SQL Server Instance

To specify the port when connecting to a SQL Server instance, use a comma to separate the server name or IP and the port. For example:

ServerName,4321

Or you can use IP and the port number:

192.168.1.22,6789

For named instances, you usually connect by specifying the server name and instance name:

ServerName\InstanceName

If you specify

Continue reading How to specify a Port Number to Connect to SQL Server Instance

How install SQL Server 32Bit on Windows 64Bit

If you have SQL Server installation media that contains both 32-bit and 64-bit version of SQL Server, you can choose to install the 32-bit SQL Server (x86) on 64-bit Windows (x64) - WOW64. On the Options tab, select x86 Processor Type:

To install 32-bit SQL Server (x86) on 64-bit Windows (x64) - WOW64, from

Continue reading How install SQL Server 32Bit on Windows 64Bit

Find all Tables with and without Identity column in SQL Server Database

To find all user tables with an Identity column in SQL Server database, use this query:

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , t.name AS Table_Name , ic.name AS Identity_Column_Name FROM sys.tables t INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id INNER JOIN sys.identity_columns ic on t.object_id = ic.object_id WHERE OBJECTPROPERTY(t.object_id,'TableHasIdentity') = 1

Continue reading Find all Tables with and without Identity column in SQL Server Database

Find all Filtered Indexes in SQL Server Database

To find all filtered indexes in all user tables in a SQL Server database, use the following query:

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , o.name AS Table_Name , o.type_desc AS 'Table_Type' , i.name AS Filtered_Index_Name , i.filter_definition , i.type_desc AS Index_Type FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id

Continue reading Find all Filtered Indexes in SQL Server Database

Find all Indexes that have Included Columns in SQL Server Database

To find all indexes in all user tables in a SQL Server database that have included columns, you can use this query:

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , o.name AS Table_Name , o.type_desc AS 'Table_Type' , i.name AS Index_Name , c.name AS 'Included_Column_Name' , i.type_desc AS Index_Type FROM sys.indexes i INNER JOIN

Continue reading Find all Indexes that have Included Columns in SQL Server Database

Find all Primary Key Columns from a SQL Server table

There can only be one primary key in a table, but it is possible to have a composite primary key - a primary key made from two or more columns.

Here is a query that will list all Primary Key columns from a SQL Server table (enter the schema and table name in the WHERE

Continue reading Find all Primary Key Columns from a SQL Server table

How to get Column names of a Table or a View in SQL Server

Here is a simple query you can use to get column names of a specified table or a view in SQL Server (replace Schema_Name and Table_Name with correct values in the query):

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Schema_Name' AND TABLE_NAME = 'Table_Name'

The INFORMATION_SCHEMA.COLUMNS view provides information about columns of all tables

Continue reading How to get Column names of a Table or a View in SQL Server

Find all Tables with Specified Column Name in a SQL Server Database

Here is a simple query you can use to find all tables and views that have a column with a specified column name in a SQL Server database:

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME ,t.TABLE_TYPE, c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE

Continue reading Find all Tables with Specified Column Name in a SQL Server Database

Find Tables without Clustered Index and with Primary Key in a SQL Server Database

When you create a primary key in SQL Server, it automatically creates a unique, clustered or non-clustered index. If you don't specify clustered or non-clustered for a primary key constraint, SQL Server will create a clustered index if there no clustered index already on the table.

If you want to find tables without Clustered Index

Continue reading Find Tables without Clustered Index and with Primary Key in a SQL Server Database

List Tables with Foreign Key Constraint in a SQL Server Database

Here is a simple query you can use to find tables with Foreign Key Constraint in a SQL Server Database with the names and schemas of the referenced tables:

SELECT fk.name AS Foreign_Key, SCHEMA_NAME(fk.schema_id) AS Schema_Name, OBJECT_NAME(fk.parent_object_id) AS Table_Name, SCHEMA_NAME(o.schema_id) Referenced_Schema_Name, OBJECT_NAME (fk.referenced_object_id) AS Referenced_Table_Name FROM sys.foreign_keys fk INNER JOIN sys.objects o ON fk.referenced_object_id =

Continue reading List Tables with Foreign Key Constraint in a SQL Server Database