How to get Column names of a Table in SQL Server

Here is a simple query you can use to get column names of a table in SQL Server:

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 and views in a database. So the query above can be used to get

Continue reading How to get Column names of a Table 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

List all User Tables With Primary Key Constraint in a SQL Server Database

To list all user tables in a SQL Server database that have a Primary Key constraint, you can use the following query:

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

Continue reading List all User Tables With Primary Key Constraint in a SQL Server Database

List all User Tables in a SQL Server Database with no Clustered Index

To list all user tables in a SQL Server database that have no Clustered Index defined, you can use the following query:

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , o.name AS Table_Name FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id WHERE i.index_id

Continue reading List all User Tables in a SQL Server Database with no Clustered Index

Find Fill Factors for Indexes in a SQL Server Database

To find what value for Fill Factor is set for all indexes for all user tables in a SQL Server database, you can use the following script:

SELECT DB_NAME() AS Database_Name , sc.name AS Schema_Name , o.name AS Table_Name , o.type_desc , i.name AS Index_Name , i.type_desc AS Index_Type , i.fill_factor FROM sys.indexes i INNER

Continue reading Find Fill Factors for Indexes in a SQL Server Database

SQL Server Remote Query Timeout Setting

Remote query timeout server configuration option in SQL Server specifies how much time in seconds will SQL Server wait for a remote query of an outgoing connection before it times out. It has no effect on incoming queries issued by clients connected to SQL Server. Query timeout is a client-side setting. An example is a

Continue reading SQL Server Remote Query Timeout Setting

Add Folder Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”

If you need to manually add folder permissions to a virtual Windows accounts, like "NT SERVICE\MSSQLSERVER", the process is a little different than adding folder permissions to other accounts.

For example you have a .bak backup file in a folder that is not accessible by SQL Server when you browse for the file, and

Continue reading Add Folder Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”

How to add Unique Constraint on multiple columns in SQL Server

You can enforce uniqueness of a column or a combination of the two or more columns using UNIQUE constraints. Creating a unique constraint automatically creates a unique index.

You can define a unique constraint when creating the table (for example on the combination of columns C1, C2 and C3):

CREATE TABLE [dbo].[test]( [ID] [int] IDENTITY(1,1)

Continue reading How to add Unique Constraint on multiple columns in SQL Server