Tech Learn

Script for All Identity Columns from all Tables SELECT * FROM SYS . IDENTITY_COLUMNS Script for All Identity Columns from particular Tables SELECT * FROM SYS . IDENTITY_COLUMNS WHERE OBJECT_ID = OBJECT_ID ( 'TABLE_NAME' )

In this article I am demonstrating the running the .sql files form command prompt. Step -1 create the table using the sql management studio. CREATE TABLE tbl_Student ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , Fname VARCHAR ( 100 ), Lname VARCHAR ( 100 ), Salary INT ) Step-2 INSERT INTO tbl_Student ( Fname , LName , Salary ) VALUES ( 'Rakesh' , 'Kalluri' , 10000 ) INSERT INTO tbl_Student ( Fname , LName , Salary ) VALUES ( 'Srujan' , 'Kumar' , 15000 ) INSERT INTO tbl_Student ( Fname , LName , Salary ) VALUES ( 'Raju' , 'Bhai' , 17000 ) INSERT INTO tbl_Student ( Fname , LName , Salary ) VALUES ( 'Dany' , 'Mark' , 18000 ) The above script file is saved in Desktop with the file name of Student.sql . The file is saved in desktop now just we need to run the file from command prompt. Step-3 Go to run - -> Type CMD - → Click OK ...

Here is the sample demonstration for reset identity column value Step-1 : Create table CREATE TABLE dbo . Emp ( ID INT IDENTITY ( 1 , 1 ), Name VARCHAR ( 10 ) ) Step-2 :Insert some sample data INSERT INTO dbo . Emp ( name ) VALUES ( 'Rakesh' ) INSERT INTO dbo . Emp ( Name ) VALUES ( 'Rakesh Kalluri' ) When we run above query the second insert statement will failed because of varchar(10) length. Step-3 :Check the identity column value DBCC CHECKIDENT ( 'Emp' ) Even second insert was failed but the identity value is increased .if we insert the another record the identity value is 3 INSERT INTO dbo . Emp ( Name ) VALUES ( 'Kalluri' ) SELECT * FROM Emp Step-4: Reset the identity column value DELETE FROM EMP WHERE ID = 3 DBCC CHECKIDENT ( 'Emp' , RESEED , 1 ) INSERT INTO dbo . Emp ( Name ) VALUES ( 'Kal...

Here is the simple script for backup all database except system database. declare @DbBackup varchar ( max )=null select @DbBackup = coalesce ( @DbBackup + char ( 10 )+ char ( 13 ), '' )+ 'backup database ' + Name + ' to disk=''D:\Backup\' + name + CONVERT ( varchar ( 10 ), getdate (), 112 )+ '.BAK''' from sys . databases where name not in( 'master' , 'tempdb' , 'model' , 'msdb' ) print @DbBackup

Step-1: --create Emp type as table create type Emp as table ( Id int , Name varchar ( 50 ) null) Step-2: --create stored procedure with table variable parameter create proc sp_GetEmp ( @In_emp as dbo . Emp readonly ) as begin set nocount on select * from @In_emp set nocount off end Step-3: --Insert some dummy data declare @temp_emp as Emp insert into @temp_emp values ( 1 , 'rakesh' ),( 2 , 'raki' ),( 3 , 'ramu' ),( 4 , 'raju' ) Step -4: --exec stored procedure exec sp_GetEmp @temp_emp

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...

--Creating the Student Table CREATE TABLE Student ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , FirstName VARCHAR ( 50 ), LastName VARCHAR ( 50 ), Marks INT ) --Inerting the some sample data INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'rakesh' , 'kalluri' , 500 ) INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'Ali' , 'MD' , 600 ) INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'Raju' , 'Ganga' , 700 ) --create procedure with out parameters CREATE PROCEDURE Get_StudentInformation AS BEGIN SELECT * FROM Student END --create Student table in Tempdb CREATE TABLE Tempdb . #Student ( ID INT , FirstName VARCHAR ( 50 ), LastName VARCHAR ( 50 ), Marks INT ) --Inserting data from stored procedure to Temp Table INSERT INTO Tempdb . #Student EXEC Get_StudentInformation -- select d...