Category Archives: SQL Server

List All Constraints of Database or Table in SQL Server

A constraint is defined as a specific rule applied to data in a table in such a way when the constraint is violated, the action is aborted. Constraint can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

Many times when we use action like: INSERT, UPDATE, DELETE, we get error(s) because on or the other constraint is violated. It’s getting annoying when we are new to this database and don’t know what constraints are set on the tables.

In this article, we will discuss about how to list all the constraints in a database. All the methods given have been tested on SQL Server 2012.

Method 1: Using sys.objects

sys.objects is a special table which contains row for each user-defined, schema-scoped object that is created within a database. Including our constraint.

All we need to do is query that table.

-- To Display all the Constraints in the Database
SELECT *
FROM [sys].[objects]
WHERE [type_desc] LIKE '%CONSTRAINT'

The above query will display all the fields of sys.objects. For convenience use, we can refine it to display in elegant way.

-- To Display all the Constraints in the Database
SELECT OBJECT_NAME(object_id) AS [ConstraintName],
       SCHEMA_NAME(schema_id) AS [SchemaName],
       OBJECT_NAME(parent_object_id) AS [TableName],
       [type_desc] AS [ConstraintType]
FROM [sys].[objects]
WHERE [type_desc] LIKE '%CONSTRAINT'

We can also display all the constraints of a table, let say ‘NEST_User’, by querying:

-- To Display all the Constraints in table 'Nest_User'
SELECT OBJECT_NAME(object_id) AS [ConstraintName],
       SCHEMA_NAME(schema_id) AS [SchemaName],
       [type_desc] AS [ConstraintType]
FROM [sys].[objects]
WHERE [type_desc] LIKE '%CONSTRAINT' AND OBJECT_NAME(parent_object_id)='NEST_User'

Method 2: Using INFORMATION_SCHEMA.TABLE_CONSTRAINTS

information schema view is one of several methods SQL Server provides for obtaining metadata. It provides an internal, system table-independent view of the SQL Server metadata. However, it has one shortcoming; it won’t display the default constraints of the database.

– To Display all the Constraints in the Database

SELECT * 
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]

– To Display all the Constraints in table ‘NEST_User’
SELECT * 
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
WHERE [TABLE_NAME]='NEST_User'

To display DEFAULT constraint in the database we can use following SQL statement.

– To Display Default Constraints in Database

SELECT OBJECT_NAME(PARENT_OBJECT_ID) AS [TABLE_NAME],
       [COL_NAME] (PARENT_OBJECT_ID, PARENT_COLUMN_ID) AS [COLUMN_NAME],
       [NAME] AS [DEFAULT_CONSTRAINT_NAME]
FROM [SYS].[DEFAULT_CONSTRAINTS]

Installing SQL Server 2012 on Windows Server 2012

Microsoft SQL Server, also called as SQL Server only, is a relational database management system (rdbms) developed by Microsoft. It is used to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). SQL Server use structured query language (SQL), technically they are T-SQL and ANSI SQL.

Some applications are likely high-coupled with Microsoft SQL Server. In simple term, they are works with SQL Server around. This is normal for Windows applications.

In this article we will discuss about how to install Microsoft SQL Server 2012 on Windows Server 2012. SQL Server 2012 has four editions: Enterprise, Standard, Developer, and Express edition. We will use Microsoft SQL Server 2012 Developer edition. This edition only suitable for development process. However this is enough, as the installation process is roughly same for any edition.

Another Extra Info

SQL Server Standard and Enterprise Edition usually used for production, while Developer Edition used for development. If we use one of them, we can also install another tool set beside SQL Server DBMS, for example: SQL Server Reporting Service, SQL Server Analysis Service, and SQL Server Integration Service.

Stage 1: Add New Username to Active Directory

If you have installed Active Directory, you should add new username in your active Directory. This username is used for SQL Server and also this step is always done if you want to create a bigger service such as SharePoint. The username for SQL Service is service and different with other username, so we will create it to new Organizational Unit (OU). Let name it as WINDEV (My machine’s NetBIOS name) so our username would be WINDEV\SQL_Service.

Open “Start screen” and choose “Active Directory Users and Computers“. Right click to your domain controller, in my case it is “windev.xathrya.web.id”. Navigate to New > Organizational Unit. Then create the name you prefer.

create-new-ou

create-new-ou-2

Then, create new user under WINDEV, SQL_Service

create-new-user

Click Next and set the password. Don’t forget to uncheck “User must change the password at next logon” and check “Password never expires”.

Stage 2: SQL Server Installation

If you have the DVD, insert it to tray. If you have the ISO, mount it. Either way, you should have opened and see a setup executable file.  Run it and wait until the installation window appears.

sql-server-1

Click on “Installation” on left pane. Then, click on “New SQL Server stand-alone installation or add features to an existing one”. Wait for next page.

sql-server-2

SQL Server installer will do some checks. It will be decided whether your machine is fulfilling the prerequisites or not for installation.

sql-server-3

Next, enter the product key. As said before, we are using SQL Server 2012 Developer Edition.

sql-server-4

The next screen prompts us to accept the license terms. We can also opt to send anonymous feature usage data to Microsoft.  It is recommended to do this as Microsoft actually uses this data to qualify and prioritize future development efforts.

sql-server-5

SQL Server also checks for updates. However we can exclude the update for now.

sql-server-6

Setup then checks for conditions that may interfere with the installation of setup support files:

sql-server-7

On above window, I would install the SQL Server on same machine as my Domain Controller. In the real world, actually we don’t do this. We have separate machine for Domain Controller and SQL Server.

Next select the setup role in the installation process. We choose “All Features With Defaults,”

sql-server-8

Click next to continue to the Feature Selection. The content would be populated based on our machine. We also install it to default path, so we won’t lay our finger there.

sql-server-9

Setup next checks installation rules.

sql-server-10

Next we determine the instance ID. This is the ID for SQL Server in our machine. It should be unique if it is used in production level when more than one SQL Server is used. In our case, we are only using it for developing so we won’t mess with it.

sql-server-11

Setup will calculate the disk space. Roughly 7GB is required when we install all the features (default features).

Next we do service account configurations. We use WINDEV\SQL_Service for all account, and remember to set the password.

sql-server-12

Database engine configuration includes Authentication Mode and SQL Server Administrators. You can accept the defaults if you want. We use Mixed Mode Authentication (combined with very strong passwords) for my installations. Clicking the Add Current User button adds me to the SQL Server administrators

sql-server-13

Next we have Analysis Services setup. I mostly work with Tabular Model these days.  As before, I click the “Add Current User” button to add this account to the Administrators:

sql-server-14

Accept the defaults for Reporting Services configuration:

sql-server-15

Click on “Add Current User” button.

sql-server-16

Give DRC (Distributed Replay Client) a name. In this case, I use XathWinServ2012Sql:

sql-server-17

Next we have Error Reporting page. Just click on Next.

Then, Installation configuration rules are checked for consistency and readiness.

sql-server-18

Next we will be presented by a confirmation page. When you decided to accept the configuration, click Install to begin installation.

sql-server-19

Installation will take some times. You should grab some food or doing something else.

sql-server-20

When the process come to end, it’s time to celebrate it. You have installed the SQL Server 2012.

sql-server-21