2008-09-16
2008-08-01
2008-06-29
2008-05-11
2008-04-19
2008-01-15
2007-11-30
2007-09-27
2007-09-26
2007-09-25
2007-09-24
2007-09-21
2007-09-19
2007-09-14
2007-08-20
2007-06-29
2007-06-12
» Edit navigation panel
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
http://sscheralbidar.blog.co.uk http://sscheral.atwiki.com http://sscheral.spaces.live.com/ =============================================================================================================
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