Home / Database / Database level Queries & Identity functions
Database

Database level Queries & Identity functions

Get list of tables in the Database:

We can get all the tables in the database by using one of the following queries.

  • SELECT * FROM SYS.OBJECTS WHERE TYPE=’u’
  • SELECT * FROM INFORMATION_SCHEMA.TABLES

Get list of columns of a Table:

To get the list of columns present in the table, we can use any of the following queries.

  • SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(‘FRIENDDETAILS’)
  • SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’FRIENDDETAILS’

Get list of constraints on a Table

Use following store procedure in order to find the constraint as well as columns names of a table.

  • To get the constraints list : SP_HELPCONSTRAINT N’FRIENDDETAILS’
  • To get the columns with constraints list : SP_HELP N’FRIENDDETAILS’

Identity on a Table

Identity can be imposed on a column to generate values for a column/table in a sequential order while performing insert operation on a column/table. For imposing identity we have to pass seed & increment factor to the Identity. Seed will be the initial start value for a column & increment factor will be the values to add to the seed value to generate new value (seed vale successor value). You can specify identity on only one columns in a table. The identity column values cannot be allocated to the identity column using the insert sentence.

Example is as below. Following example creates a table ‘FriendDetails’ with imposed identity on ‘Id’ column

  • CREATE TABLE [FriendDetails] ([Id] [int] primary key IDENTITY(1,1),

[Firstname] [nvarchar](250) ,[Lastname] [nvarchar](250) ,[Middlename] [nvarchar](250) )

Here seed value is 1 & increment factor is also 1. So first Id values will be 1 & second will be 2 & so on.

Above description solves following questions.

  1. How to apply identity on a column?
  2. How to apply identity on a table?

Identity functions

We can get the identity information available for a table using following functions. The system variable “@@Identity” returns the identity value generated by last insert statement.

  1. Ident_seed(‘table_name’)

This function returns identity seed values present on the table.

  1. Ident_incr(‘table_name‘)

This function is used to get the increment value available on a given table.

  1. Ident_current(‘table_name’)

This function returns current identity value available on the table.

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Delete Command

Difference between Delete and Truncate command

Difference between Delete and Truncate command Delete Command 1. It allows to delete all rows as ...

Leave a Reply