Category: SQL

Get Table Detail

DECLARE @table_name SYSNAME SELECT @table_name = ‘dbo.TableName’   DECLARE @object_name SYSNAME , @object_id INT   SELECT @object_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’ , @object_id = o.[object_id] FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE s.name + ‘.’ + o.name = @table_name AND o.[type] = ‘U’ AND o.is_ms_shipped =… Read more →

Get all foreign key constent from schema

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME… Read more →

Assignment Operator

DECLARE @test INT; Truncate table #TableName SET @test = 1; SET @a = ( SELECT TableNameID FROM #TableName where (1=2)); Print @a — result NULL SET @test = 1; SELECT @a=TableNameID FROM #TableName where (1=2); Print @a — result 1 Read more →

SQL Server: Get foreign key dependencies

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME… Read more →

A fatal scripting error occurred

A fatal scripting error occurred.incorrect syntax was encountered while parsing go use separate line for bulk comment with Go Like Select 1 Go/* comment*/ Will not work. change it like: Select 1 Go /* comment*/ Read more →

sql query updated

–update table with swap between two columns update mmm_1 set id=phone,phone=id; –delete duplicate value from data table and select not duplicate also with x as( select empid,lname,deptno,rn=ROW_NUMBER() over (partition by deptno order by deptno),rn1=dense_rank() over (partition by deptno order by deptno), rn2=Rank() over ( order by deptno) from employees) delete x where x.rn>1; –delete all duplicate values delete employees where… Read more →

Get Words from NUMBER

— select dbo.fnNumberToWords(2323422435);   Alter FUNCTION fnNumberToWords(@Number as BIGINT)  RETURNS VARCHAR(1024) AS BEGIN DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32)) DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32)) INSERT @Below20 (Word) VALUES ( ‘Zero’), (‘One’),( ‘Two’ ), ( ‘Three’), ( ‘Four’ ), ( ‘Five’ ), ( ‘Six’ ), ( ‘Seven’ ), ( ‘Eight’), ( ‘Nine’), ( ‘Ten’), ( ‘Eleven’… Read more →

Updated sql questions

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like: @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ. Compare @@IDENTITY It returns… Read more →