Welcome to Shivshanker's Blog

Important SQL Code

how to retriev the first 10 records from table in sql server with out using
top key word( duplication should not be find)
select distinct a.questionid,a.question,a.questiongroup from questions a,
questions b where a.questionid>=b.questionid group by
a.questionid,a.question,a.questiongroup 
having count(*)<=10
select distinct a.* from table1 a, table1 b where a.id>=b.id group by
a.id,a.col1,a.col2 
having count(*)<=10 
=============================================================================================================
How can I tell whether a table is a system (Microsoft) table in SQL 7? 
select * from sysobjects where objectproperty(id, 'IsMSShipped') = 1 
============================================================================================================= 
Master DB
 It holds all the info reg the databases available in that server. 
It also holds the information abt physical paths,the various groups of mdf,ldf
files of the databases.
TempDB
 It is used for creating/storing the temporary tables /stored procedures
used in the stored procedures. 
If the SQL server is restarted then all the tables /stored procedures are
cleared and the entire db is reintialized.
MSDB
 It is used by SQL Server Agent for scheduling jobs. 
To store the information reg the Restore/Backup and the devices used for
Restore/Backup ,the location of the backups etc 
============================================================================================================= 
All of the available collations can be returned by using the
fn_helpcollations() function, for example:
SELECT * FROM ::fn_helpcollations()
-- Case Insensitive compare 
SELECT * 
FROM crmusers 
WHERE UserPassword = 'A' COLLATE SQL_Latin1_General_CP1_CI_AS 
-- Case Sensitive Compare 
SELECT * 
FROM crmusers 
WHERE UserPassword = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS 
============================================================================================================= 
-- To get identity columns from a table
SELECT @IdentityColumnName = Name FROM syscolumns WHERE status = 128 AND
ID=object_id(@TableName) 
============================================================================================================= 
-- to get Identity column details such as seed and current increment value
select ident_seed(name),ident_incr(name) as seed ,name,id,xtype from
syscolumns where ident_seed(name) is not null
-- to reset the identity seed
DBCC CHECKIDENT('leads63', RESEED, 1)
To set Identity seed to any number use this following command
DBCC CHECKIDENT('tablename', RESEED, 10) 
'Tablename is table name which you want to reseed and 10 the start seed for the
table means it start from 10 it inserts next record with id 11
,12,...
-- to set identity insertion on , off
SET IDENTITY_INSERT Yaks ON 
Insert Yaks (YakID, YakName) Values(1, 'Mac the Yak')
SET IDENTITY_INSERT Yaks OFF
select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'leads%'

============================================================================================================= 
Some SQL Queries 
Here are some useful sql queries
 
How to Run commands for all tables in current dB:
EXEC sp_MSforeachtable 
@command1 = 'DELETE FROM ? WHERE your_condition'
The '?' will be replaced by the table name 
-------------------------------------------------------------------------------- 
How to find Size of all user tables with the number of rows:
EXEC sp_MSforeachtable 
@command1='sp_spaceuse d "?"'

--------------------------------------------------------------------------------
How to find Number of rows in a table and the space the table and 
index use (in KB):
EXEC sp_MStablespace table_name

--------------------------------------------------------------------------------
How to display Size of the datafile and log separately:
EXEC sp_helpdb database_name

--------------------------------------------------------------------------------
How to List of all hard drives and the amount of free space in MB 
for each drive:
EXEC master..xp_fixeddrives

--------------------------------------------------------------------------------
How to return all tables, which contain a specified column:
SELECT a.name 'Table' 
FROM sysobjects a 
JOIN syscolumns b 
ON a.id = b.id 
WHERE a.type = 'U' AND 
b.name = + 'your_column'

--------------------------------------------------------------------------------
How to Delete a file from the server in SQL:
xp_cmdshell "cmd /c del filename_with_path "

--------------------------------------------------------------------------------
How to Control State Options of the Database:
ALTER DATABASE db_name 
SET SINGLE_USER 
WITH ROLLBACK AFTER x SECONDS
or
ALTER DATABASE db_name 
SET RESTRICTED_USER 
(or MULTI_USER) 
WITH ROLLBACK IMMEDIATE
(Roll back transactions after the specified number of seconds or 
immediately.)

=============================================================================================================
=
You often need a bulk insert with dynamic primary key where you can define
your keys without getting it from the .txt files or .csv files.
here are the stored procedure to bulk insert country with dynamic primary
key.
first create temp.txt file in c drive with following data.
Australia 
Canada 
America 
India
then create table with following fields
countryid int 4 
countryname varchar(100)
and assign name as tblCountryMaster
now use following stored procedure
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE sp_BulkInsertCountry 
( 
@FilePath varchar(1000) 
) 
AS 
BEGIN--PROCEDURE 
--variable declaration 
declare @SQL varchar(500) 
declare @id int 
declare @CountryName varchar(30)
 
--Create temporary table for Country 
CREATE TABLE #tmpCountry 
( 
CountryName varchar(30), 
)
---executing bulk insert on temporary table 
SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH
(FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')' 
EXEC(@sql)
DECLARE cursor_Country CURSOR READ_ONLY FOR 
select [CountryName] from #tmpCountry
OPEN cursor_Country 
FETCH NEXT FROM cursor_Country INTO @CountryName 
WHILE @@FETCH_STATUS=0 
BEGIN 
SELECT @id=isnull(max(Countryid),0) from tblCountryMaster 
SET @id=@id+1 
INSERT INTO tblCountryMaster values(@Id,@CountryName) 
FETCH NEXT FROM cursor_Country INTO @CountryName 
END 
CLOSE cursor_Country 
DEALLOCATE cursor_Country 
END--PROCEDURE
 
GO 
SET QUOTED_IDENTIFIER OFF 
GO 
SET ANSI_NULLS ON 
GO
happy programming

=============================================================================================================
= 
--Gives the new empty record
select * FROM Customers WHERE CustomerId = 'NewID' 
=============================================================================================================
= 
to display the field like 'Relation'. means first letter should be a uppercase
and 
remaining letters are lower case. to use this sql query to get from DB.
SELECT UPPER(LEFT(Column_Name, 1))+LOWER(SUBSTRING(Column_Name, 2,
(LEN(Column_Name) - 1))) FROM List 
=============================================================================================================
= 
Q How can I list out database tables which have no records? 
Ans.
declare @strsql varchar(256) 
create table #emptytables (tablename varchar(128), table_rowcount int)
select @strsql='select distinct o.name as TableName, x.rowcnt as
Table_RowCount 
from sysobjects o 
inner join sysindexes x 
on o.id = x.id 
where x.rowcnt = 0 and 
o.type = ''U'''
insert #emptytables (TableName, Table_rowcount) exec (@strsql) 
select * from #emptytables 
drop table #emptytables 
=============================================================================================================
= 
Question:-How do you programmatically find out when the SQL Server service
started?
Answer:-There is one simple but awkward method is there to find out when the
SQL server service is started.
The following query will give you the time when the SQL server service is
started;
select crdate from [master].dbo.sysdatabases 
where name=’tempdb’
This is because, the tempdb will be created whenever SQL server service is
started or restarted. 
=============================================================================================================
= 
EXEC sp_helptext vwProductSales 
--This stored procedure is used to get the depends details means reference key,
foreign keys etc... 
EXEC sp_depends vwProductSales 
EXEC sp_depends products 
=============================================================================================================
= 
select rows from 5 to 15 without id coloumn 
First take top 15 records and minus top 5 records.
select top 10 * from Emp where eno not in(select top 5 eno from Emp) 
=============================================================================================================
= 
gives percentage of rows 
TOP n [PERCENT] 
n specifies how many rows are returned. If PERCENT is not specified, n is the
number of rows to return. If PERCENT is specified, n is the percentage
of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */ 
TOP 15 PERCENT /* Return the top 15% of the result set. */

=============================================================================================================
= 
To select duplicate row count from the table 
select count(name),name from list 
 group by name 
 having count(name) > 1
to delete duplicate rows from the table 
delete from list where (id) not in (select min(id) as id from list group by
name)
to delete without id table 
SET ROWCOUNT 1 DELETE testing 
FROM testing a 
WHERE (SELECT COUNT(*) 
 FROM testing b 
 WHERE b.id = a.id AND b.name = a.name) > 1 WHILE
@@rowcount > 0 DELETE testing 
FROM testing a 
WHERE (SELECT COUNT(*) 
 FROM testing b 
 WHERE b.id = a.id AND b.name = a.name) > 1 
SET ROWCOUNT 0
-- to delete without id table by Checking two fields id and title 
SET ROWCOUNT 1 
DELETE JOB FROM JOB a 
WHERE (SELECT COUNT(*) FROM JOB b 
WHERE b.id = a.id AND b.TITLE = a.title) > 1 
WHILE @@rowcount > 0 
DELETE job FROM job a 
WHERE (SELECT COUNT(*) FROM job b 
WHERE b.id = a.id AND b.title = a.title) > 1 
SET ROWCOUNT 0
-- to delete without id table by Checking only one field title 
SET ROWCOUNT 1 
DELETE JOB FROM JOB a 
WHERE (SELECT COUNT(*) FROM JOB b 
WHERE b.TITLE = a.title) > 1 
WHILE @@rowcount > 0 
DELETE job FROM job a 
WHERE (SELECT COUNT(*) FROM job b 
WHERE b.title = a.title) > 1 
SET ROWCOUNT 0

=============================================================================================================
= 
--To get the comma seperated string from a table
Declare @str Varchar(100) 
-- Forming the comma separated string 
Select @str= COALESCE(@str+',' , '') + Val from vin_temp 
Select @str 
=============================================================================================================
= 
--To get stored proecedure detail using query we can use this command
SELECT OBJECT_DEFINITION (OBJECT_ID (' < name of SP > ') )
where <name of SP> is Stored Procedure Name (SQL 2005) 
=============================================================================================================
= 
To transfer data from one server to another server by using Query 
1) First Execute the Stored procedure
sp_addlinkedserver <ServerName>
e.g. sp_addlinkedserver Ravi1
2) Then execute the query 
 
Select * from Server.Database.Owner.Table
e.g. select * from Ravi1.Stars_db.dbo.validsurveys

=============================================================================================================
= 
-- select data from remote server on the internet using ip adress
use [ipaddress].database_name.dbo.table_name
1) sp_addlinkedserver [remoteserver Name]
2) Select ID from TableName 
Where ID not in (Select ID from 
[20.10.0.233].Database.dbo.TableName)
Order to run the query
Run the 1) query 
Run the 2) query.

=============================================================================================================
= 
Selecting nth highest salary in bellow query change the number 
6 to any position then it gives highest salary of that position -1 
means if it 6 then it gives 7th highest salary 
if it 4 then it gives 5th highest and so on.
select salary from emp e 
where 6 = ( Select count(distinct(sal)) from emp e1 where e.sal <
e1.sal)
-- selects nth highest salary bellow ine picks 6th highest salary 
select Top 1 sal from emp where sal in(Select Top 6 sal from emp order by desc)
order by sal ASC
select MIN(sal) from emp where sal in(Select Top 6 sal from emp order by
desc)
--We can get random records every time by using this query 
select top 5 * from answer order by newid() 
=============================================================================================================
=Microsoft introduced the EXCEPT operator in SQL Server 2005, which returns
all of the distinct rows from the left side of the EXCEPT operator. It
also removes all of the rows from the result set that match the rows that
are on the right side of the EXCEPT operator.
eg:
SELECT C1,C2 FROM T1 EXCEPT SELECT C1,C2 FROM T2 
SELECT C1,C2 FROM T2 EXCEPT SELECT C1,C2 FROM T1
SELECT c1,c2 FROM t1 INTERSECT SELECT c1,c2 FROM t2

=============================================================================================================
=-- selecting all column names from all tables
Select TABLE_CATALOG = Left(TABLE_CATALOG, 10), 
TABLE_SCHEMA = Left(TABLE_SCHEMA, 10), 
TABLE_NAME = Left(TABLE_NAME, 10), 
COLUMN_NAME = Left(COLUMN_NAME, 20) 
From stars_db.Information_Schema.Columns 
Where Table_Name like 'S000%'
Select TABLE_CATALOG , 
TABLE_SCHEMA, 
TABLE_NAME , 
COLUMN_NAME 
From stars_db.Information_Schema.Columns 
Where Table_Name like 'S000%' AND COLUMN_NAME LIKE 'ValidC%'

=============================================================================================================
=select 
1 A 
2 B 
3 C
like A,B,C........ 
declare @n varchar(1500) 
set @n='' 
select @n=@n + convert(varchar(10),QuestionID) + ',' from answer 
select @n
declare @str nVARCHAR(4000) 
select @str = COALESCE(@str+',','Start ') + convert(varchar(10),QuestionID)
from answer 
select @str

=============================================================================================================
=-- To get Table Created and modified date 
Select name,create_date,modify_date From sys.tables

=============================================================================================================
<a href=
"http://sscheralbidar.blog.co.uk">http://sscheralbidar.blog.co.uk</a> 
<a href="http://sscheral.atwiki.com">http://sscheral.atwiki.com</a> 
<a href=
"http://sscheral.spaces.live.com/">http://sscheral.spaces.live.com/</a> 
=============================================================================================================
 
In-Built Functions 
AT(): Returns the beginning numeric position of the nth occurrence of a
character expression within another character expression, counting from
the leftmost character. 
RAT(): Returns the numeric position of the last (rightmost) occurrence of a
character string within another character string. 
OCCURS(): Returns the number of times a character expression occurs within
another character expression (including overlaps). 
OCCURS2(): Returns the number of times a character expression occurs within
another character expression (excluding overlaps). 
PADL(): Returns a string from an expression, padded with spaces or characters
to a specified length on the left side. 
PADR(): Returns a string from an expression, padded with spaces or characters
to a specified length on the right side. 
PADC(): Returns a string from an expression, padded with spaces or characters
to a specified length on the both sides. 
CHRTRAN(): Replaces each character in a character expression that matches a
character in a second character expression with the corresponding
character in a third character expression. 
STRTRAN(): Searches a character expression for occurrences of a second
character expression, and then replaces each occurrence with a third
character expression. Unlike a built-in function Replace, STRTRAN has three
additional parameters. 
STRFILTER(): Removes all characters from a string except those specified. 
GETWORDCOUNT(): Counts the words in a string. 
GETWORDNUM(): Returns a specified word from a string. 
GETALLWORDS(): Inserts the words from a string into the table. 
PROPER(): Returns from a character expression a string capitalized as
appropriate for proper names. 
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right
search. 
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified
numeric expression (from 1 to 3999). 
ROMANTOARAB(): Returns the number equivalent of a specified character Roman
numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL
INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

=============================================================================================================
=inbuilt functions
SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees;
SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName 
FROM Employees
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees
SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS
PortionOfLastNameMirrorImage 
FROM Employees
SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle,
Title 
FROM Employees
SELECT REPLICATE('MyCoolString', 5)
SELECT SPACE(12) AS Spaces
SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2,
(LEN(FirstName) - 1))) + ' ' 
+ UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) -
1))) 
AS FullName 
FROM Employees
SELECT 'My Output' + CHAR(10) + CHAR(13) 
+ 'AnotherOutput' 
=============================================================================================================
=www.TransactSQL.Com

menu

recent list 20

RSS Feeds

Important SQL Code