How to find all the IDENTITY columns in a database?

Here is the easiest way to list all the IDENTITY Columns of any database:

SELECTOBJECT_NAME([object_id]) as TableName,

name as ColumnName

FROM [DatabaseName].sys.columns

WHERE is_identity = 1

What is Stored Procedure?

A stored procedure is the saved collection of T-SQL statements in the SQL Server database that can take and return user-supplied parameters. Stored Procedures (also called sproc) can have input parameters and output parameters. Same stored procedure can be used over the network by several clients by passing different input data for input parameters. When a stored procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.

Here is the syntax of SPROC:

CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
   T-SQL-statements
END

Here is an example:
CREATE PROC ProductList (
   @ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
   SELECT
     ProductName, ProductType,
     Model, UnitPrice
   FROM Products (NOLOCK)
   WHERE ProductType = @ProductType
END

How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO

-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO


SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS

Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.

Function to Convert Decimal Number into Binary, Ternary, and Octal

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO


CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
  decNum [int] PRIMARY KEY NOT NULL,
  NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
  WITH tblBase AS
  (
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS VARCHAR(50))
    FROM tblBase WHERE baseNum < @base-1
  ),
  numbers AS
  (
    SELECT CAST(baseNum AS VARCHAR(50)) AS num
    FROM tblBase
    UNION ALL
    SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
  )

  INSERT INTO @NumbersBaseN
  SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
  FROM numbers WHERE LEN(NUM) > @lenght - 1

  OPTION (MAXRECURSION 0);
  RETURN
END
GO


-- Unit Test --
-- Example with decimal, binary, ternary and octal


SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
  ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
  ON u2.decNum = u3.decNum


Here is the output: