SQL FAQ 2

SQL FAQ

Display all tables name in SQL Server? Select * from sysobjects where xtype='U'

Single Query Update salary with condition Update Employee SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40001 AND 50000 THEN salary + 7000 WHEN salary BETWEEN 50001 AND 60000 THEN salary + 9000 END

Which TCP/IP port does the SQl Server is running? SQL Server runs on port 1433. It can be change from the Network Utility TCP/IP then enter into Properties à Port Number. Both on client and the Server.

What’s the maximum size of a row? 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

Find the Duplicate Values in a table SELECT EmpID from Employee a where (SELECT count(*) from Employee b where a.EmpID=b.EmpID) > 1

Delete duplicate Record WITH deldup as (Select row_number() over (partition by EmpID order by EmpID) as RONO, EmpID from Employee) Delete from deldup where RONO>1

How to Findout Nth highest Salary Select salary from Employee A where n=(select count(distinct salary) from Employee B where A.salary <= B.salary

Example for Group by Having Select Depart,Sum(salary) from Employee group by Depart having sum(salary) >= 1000

To copy the whole tables with data Select * into [New Table] from [Old Table] Select * into EmployeeNew from Employee

Copy structure only Create table [New Table] as select * from [Old table] where 1=2 Create table EmployeeNew as select * from Employee where 1=2

To find total row in a table USE [Database name] EXEC sp_spaceused [Table Name] USE Employee EXEC sp_spaceused Department

How Rename the table USE [Database Name] EXEC sp_rename [Old Table Name], [New Table Name] USE Employee EXEC sp_rename Depart, DepartNew

How to add new Data type for Database USE Employee EXEC sp_addtype USPhoneNumber, 'char(12)', 'NOT NULL'

To find out the login that is mapped to the built-in “dbo” user USE Employee GO SELECT b.name AS Login_Mapped_to_DBO FROM sys.database_principals a INNER JOIN sys.server_principals b ON a.sid = b.sid WHERE a.name = 'dbo'

To find out old db_owner role USE Employee GO SELECT c.name AS DB_Owner_Role_Member FROM sys.database_principals a INNER JOIN sys.database_role_members b ON a.principal_id = b.role_principal_id AND a.is_fixed_role = 1 AND a.name ='db_owner' INNER JOIN sys.database_principals c ON b.member_principal_id = c.principal_id

To get the NULL passwords Username Use master Select name,Password from syslogins where password is null order by name

To Find out the User tables in a Database SELECT * from sysobjects where xtype = 'U' SELECT * from sysobjects where xtype = 'U' and name like 'd%' SELECT * from sysobjects where name like 'd%'

To Get The Column Name,DataType, And Length of columns select column_name, data_type, character_maximum_length from information_schema.columns