Added line is this color
Deleted line is this color
-<p>All of the available collations can be returned by using the +<p>how to retriev the first 10 records from table in sql server with out using +top key word( duplication should not be find)</p> +<p>select distinct a.questionid,a.question,a.questiongroup from questions a, +questions b where a.questionid>=b.questionid group by</p> +<p>a.questionid,a.question,a.questiongroup<br> +having count(*)<=10</p> +<p>select distinct a.* from table1 a, table1 b where a.id>=b.id group by +a.id,a.col1,a.col2<br> +having count(*)<=10<br> +=============================================================================================================</p> +<p>How can I tell whether a table is a system (Microsoft) table in SQL 7?<br> +select * from sysobjects where objectproperty(id, 'IsMSShipped') = 1<br> +=============================================================================================================<br> +Master DB</p> +<p> It holds all the info reg the databases available in that server.<br> + It also holds the information abt physical paths,the various groups of mdf,ldf +files of the databases.</p> +<p>TempDB</p> +<p> It is used for creating/storing the temporary tables /stored procedures +used in the stored procedures.<br> + If the SQL server is restarted then all the tables /stored procedures are +cleared and the entire db is reintialized.</p> +<p>MSDB</p> +<p> It is used by SQL Server Agent for scheduling jobs.<br> + To store the information reg the Restore/Backup and the devices used for +Restore/Backup ,the location of the backups etc<br> +=============================================================================================================<br> +All of the available collations can be returned by using the fn_helpcollations() function, for example:</p> <p>SELECT * FROM ::fn_helpcollations()</p> -<p> </p> <p>-- Case Insensitive compare<br> SELECT *<br> FROM crmusers<br> WHERE UserPassword = 'A' COLLATE SQL_Latin1_General_CP1_CI_AS </p> -<p><br> --- Case Sensitive Compare<br> +<p>-- Case Sensitive Compare<br> SELECT *<br> FROM crmusers<br> -WHERE UserPassword = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS</p> -<p> -----------------------------------------------------------------------------------------------------------------------------------------------</p> -<p>-- To get identity columns from a table</p> -<p>SELECT @IdentityColumnName = Name<br> - FROM syscolumns<br> - WHERE status = 128 AND<br> - ID=object_id(@TableName)<br> -----------------------------------------------------------------------------------------------------------------------------------------------</p> -<p>exec sp_depend 'leads67'</p> -<p>select name,ident_seed(name) seed,ident_incr(name) incr from syscolumns -where ident_seed(name) is not null</p> -<p>select name,status from syscolumns<br> -SELECT name,status FROM syscolumns<br> - WHERE status = 128 AND<br> - id = (SELECT id FROM sysobjects WHERE name = 'leads63')</p> -<p> </p> -<p>select name,ident_seed(name) seed from sys.tables order by name</p> -<p><br> -select ident_seed(name),ident_incr(name) as seed ,name,id,xtype from syscolumns -where name like 'ii%'</p> +WHERE UserPassword = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS<br> +=============================================================================================================<br> +-- To get identity columns from a table</p> +<p>SELECT @IdentityColumnName = Name FROM syscolumns WHERE status = 128 AND +ID=object_id(@TableName)<br> +=============================================================================================================<br> +-- to get Identity column details such as seed and current increment value</p> <p>select ident_seed(name),ident_incr(name) as seed ,name,id,xtype from syscolumns where ident_seed(name) is not null</p> -<p>ident_seed(name) is not null</p> -<p>select * from leads63</p> -<p><br> -DBCC CHECKIDENT('leads63', RESEED, 1)</p> -<p>dbcc checkident('leads63')<br> -EXEC sp_depends 'leads63'<br> -select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'leads%'<br> -SELECT<br> - IDENT_SEED(TABLE_NAME) AS Seed<br> -, IDENT_INCR(TABLE_NAME) AS Increment<br> -, TABLE_NAME <br> - FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'leads%'<br> - 'TableHasIdentity') = 1<br> - AND TABLE_TYPE = 'BASE TABLE'</p> -<p>SELECT<br> - IDENT_SEED(TABLE_NAME) AS Seed<br> -, IDENT_INCR(TABLE_NAME) AS Increment<br> -, TABLE_NAME<br> - FROM INFORMATION_SCHEMA.TABLES<br> - WHERE OBJECTPROPERTY(OBJECT_ID('leads67'),<br> - 'TableHasIdentity') = 1<br> - AND TABLE_TYPE = 'BASE TABLE'</p> -<p>exec sp_help '518292906'<br> -------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> - +<p>-- to reset the identity seed</p> +<p>DBCC CHECKIDENT('leads63', RESEED, 1)</p> +<p>To set Identity seed to any number use this following command</p> +<p>DBCC CHECKIDENT('tablename', RESEED, 10)<br> +'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</p> +<p>,12,...</p> +<p>-- to set identity insertion on , off</p> +<p>SET IDENTITY_INSERT Yaks ON<br> +Insert Yaks (YakID, YakName) Values(1, 'Mac the Yak')</p> +<p>SET IDENTITY_INSERT Yaks OFF</p> +<p>select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'leads%'</p> +<p> +=============================================================================================================<br> Some SQL Queries<br> Here are some useful sql queries</p> <p><br> How to Run commands for all tables in current dB:</p> <p>EXEC sp_MSforeachtable<br> @command1 = 'DELETE FROM ? WHERE your_condition'</p> -<p>The '?' will be replaced by the table name</p> -<p> ---------------------------------------------------------------------------------</p> -<p>How to find Size of all user tables with the number of rows:</p> +<p>The '?' will be replaced by the table name<br> +--------------------------------------------------------------------------------<br> +How to find Size of all user tables with the number of rows:</p> <p>EXEC sp_MSforeachtable<br> @command1='sp_spaceuse d "?"'</p> <p> --------------------------------------------------------------------------------</p> <p>How to find Number of rows in a table and the space the table and<br> index use (in KB):</p> <p>EXEC sp_MStablespace table_name</p> <p> --------------------------------------------------------------------------------</p> <p>How to display Size of the datafile and log separately:</p> <p>EXEC sp_helpdb database_name</p> <p> --------------------------------------------------------------------------------</p> <p>How to List of all hard drives and the amount of free space in MB<br> for each drive:</p> <p>EXEC master..xp_fixeddrives</p> <p> --------------------------------------------------------------------------------</p> <p>How to return all tables, which contain a specified column:</p> <p>SELECT a.name 'Table'<br> FROM sysobjects a<br> JOIN syscolumns b<br> ON a.id = b.id<br> WHERE a.type = 'U' AND<br> b.name = + 'your_column'</p> <p> --------------------------------------------------------------------------------</p> <p>How to Delete a file from the server in SQL:</p> <p>xp_cmdshell "cmd /c del filename_with_path "</p> <p> --------------------------------------------------------------------------------</p> <p>How to Control State Options of the Database:</p> <p>ALTER DATABASE db_name<br> SET SINGLE_USER<br> WITH ROLLBACK AFTER x SECONDS</p> <p>or</p> <p>ALTER DATABASE db_name<br> SET RESTRICTED_USER<br> (or MULTI_USER)<br> WITH ROLLBACK IMMEDIATE</p> <p>(Roll back transactions after the specified number of seconds or<br> immediately.)</p> -<p><br> -==============================================================================================================</p> +<p> +=============================================================================================================</p> +<p>=</p> <p>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.</p> <p>here are the stored procedure to bulk insert country with dynamic primary key.</p> <p>first create temp.txt file in c drive with following data.</p> <p>Australia<br> Canada<br> America<br> India</p> <p>then create table with following fields</p> <p>countryid int 4<br> countryname varchar(100)</p> <p>and assign name as tblCountryMaster</p> <p>now use following stored procedure</p> -<p><br> -SET QUOTED_IDENTIFIER ON<br> +<p>SET QUOTED_IDENTIFIER ON<br> GO<br> SET ANSI_NULLS ON<br> GO</p> <p>CREATE PROCEDURE sp_BulkInsertCountry<br> (<br> @FilePath varchar(1000)<br> )<br> AS<br> BEGIN--PROCEDURE<br> --variable declaration<br> declare @SQL varchar(500)<br> declare @id int<br> declare @CountryName varchar(30)</p> <p><br> --Create temporary table for Country<br> CREATE TABLE #tmpCountry<br> (<br> CountryName varchar(30),<br> )</p> <p>---executing bulk insert on temporary table<br> SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH (FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')'<br> EXEC(@sql)</p> <p>DECLARE cursor_Country CURSOR READ_ONLY FOR<br> select [CountryName] from #tmpCountry</p> <p>OPEN cursor_Country<br> FETCH NEXT FROM cursor_Country INTO @CountryName<br> WHILE @@FETCH_STATUS=0<br> BEGIN<br> SELECT @id=isnull(max(Countryid),0) from tblCountryMaster<br> SET @id=@id+1<br> INSERT INTO tblCountryMaster values(@Id,@CountryName)<br> FETCH NEXT FROM cursor_Country INTO @CountryName<br> END<br> CLOSE cursor_Country<br> DEALLOCATE cursor_Country<br> END--PROCEDURE</p> <p><br> GO<br> SET QUOTED_IDENTIFIER OFF<br> GO<br> SET ANSI_NULLS ON<br> GO</p> <p>happy programming</p> -<p> </p> <p> -===================================================================================================================================<br> - -Gives the new empty record</p> +=============================================================================================================</p> +<p>=<br> +--Gives the new empty record</p> <p>select * FROM Customers WHERE CustomerId = 'NewID'<br> -==============================================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> to display the field like 'Relation'. means first letter should be a uppercase and<br> remaining letters are lower case. to use this sql query to get from DB.</p> <p>SELECT UPPER(LEFT(Column_Name, 1))+LOWER(SUBSTRING(Column_Name, 2, (LEN(Column_Name) - 1))) FROM List<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> Q How can I list out database tables which have no records?<br> Ans.</p> <p>declare @strsql varchar(256)<br> create table #emptytables (tablename varchar(128), table_rowcount int)</p> <p>select @strsql='select distinct o.name as TableName, x.rowcnt as Table_RowCount<br> from sysobjects o<br> inner join sysindexes x<br> on o.id = x.id<br> where x.rowcnt = 0 and<br> o.type = ''U'''</p> <p>insert #emptytables (TableName, Table_rowcount) exec (@strsql)<br> select * from #emptytables<br> drop table #emptytables<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> Question:-How do you programmatically find out when the SQL Server service started?</p> <p>Answer:-There is one simple but awkward method is there to find out when the SQL server service is started.</p> <p>The following query will give you the time when the SQL server service is started;</p> <p>select crdate from [master].dbo.sysdatabases<br> where name=’tempdb’</p> <p>This is because, the tempdb will be created whenever SQL server service is started or restarted.<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> EXEC sp_helptext vwProductSales<br> --This stored procedure is used to get the depends details means reference key, foreign keys etc...<br> EXEC sp_depends vwProductSales<br> EXEC sp_depends products<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> select rows from 5 to 15 without id coloumn<br> First take top 15 records and minus top 5 records.</p> <p>select top 10 * from Emp where eno not in(select top 5 eno from Emp)<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> gives percentage of rows<br> TOP n [PERCENT]<br> 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:</p> +number of rows to return. If PERCENT is specified, n is the percentage</p> +<p>of the result set rows to return:</p> <p>TOP 120 /*Return the top 120 rows of the result set. */<br> TOP 15 PERCENT /* Return the top 15% of the result set. */</p> <p> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> To select duplicate row count from the table<br> select count(name),name from list<br> group by name<br> having count(name) > 1</p> <p>to delete duplicate rows from the table<br> delete from list where (id) not in (select min(id) as id from list group by name)</p> <p>to delete without id table<br> SET ROWCOUNT 1 DELETE testing<br> FROM testing a<br> WHERE (SELECT COUNT(*)<br> FROM testing b<br> WHERE b.id = a.id AND b.name = a.name) > 1 WHILE @@rowcount > 0 DELETE testing<br> FROM testing a<br> WHERE (SELECT COUNT(*)<br> FROM testing b<br> WHERE b.id = a.id AND b.name = a.name) > 1<br> SET ROWCOUNT 0</p> <p>-- to delete without id table by Checking two fields id and title<br> SET ROWCOUNT 1<br> DELETE JOB FROM JOB a<br> WHERE (SELECT COUNT(*) FROM JOB b<br> WHERE b.id = a.id AND b.TITLE = a.title) > 1<br> WHILE @@rowcount > 0<br> DELETE job FROM job a<br> WHERE (SELECT COUNT(*) FROM job b<br> WHERE b.id = a.id AND b.title = a.title) > 1<br> SET ROWCOUNT 0</p> <p>-- to delete without id table by Checking only one field title<br> SET ROWCOUNT 1<br> DELETE JOB FROM JOB a<br> WHERE (SELECT COUNT(*) FROM JOB b<br> WHERE b.TITLE = a.title) > 1<br> WHILE @@rowcount > 0<br> DELETE job FROM job a<br> WHERE (SELECT COUNT(*) FROM job b<br> WHERE b.title = a.title) > 1<br> SET ROWCOUNT 0</p> <p> -==============================================================================================================<br> - -to get the comma seperated string from a table</p> +=============================================================================================================</p> +<p>=<br> +--To get the comma seperated string from a table</p> <p>Declare @str Varchar(100)<br> -- Forming the comma separated string<br> Select @str= COALESCE(@str+',' , '') + Val from vin_temp<br> Select @str<br> -==============================================================================================================<br> - -to get stored proecedure detail using query we can use this command</p> +=============================================================================================================</p> +<p>=<br> +--To get stored proecedure detail using query we can use this command</p> <p>SELECT OBJECT_DEFINITION (OBJECT_ID (' < name of SP > ') )</p> <p>where <name of SP> is Stored Procedure Name (SQL 2005)<br> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> To transfer data from one server to another server by using Query<br> 1) First Execute the Stored procedure</p> <p>sp_addlinkedserver <ServerName></p> <p>e.g. sp_addlinkedserver Ravi1</p> <p>2) Then execute the query<br> <br> Select * from Server.Database.Owner.Table</p> <p>e.g. select * from Ravi1.Stars_db.dbo.validsurveys</p> <p> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> -- select data from remote server on the internet using ip adress</p> <p>use [ipaddress].database_name.dbo.table_name</p> <p>1) sp_addlinkedserver [remoteserver Name]</p> <p>2) Select ID from TableName<br> Where ID not in (Select ID from<br> [20.10.0.233].Database.dbo.TableName)</p> <p>Order to run the query</p> <p>Run the 1) query<br> Run the 2) query.</p> -<p> </p> <p> -==============================================================================================================<br> - +=============================================================================================================</p> +<p>=<br> Selecting nth highest salary in bellow query change the number<br> 6 to any position then it gives highest salary of that position -1<br> means if it 6 then it gives 7th highest salary<br> if it 4 then it gives 5th highest and so on.</p> <p>select salary from emp e<br> where 6 = ( Select count(distinct(sal)) from emp e1 where e.sal < e1.sal)</p> -<p><br> --- selects nth highest salary bellow ine picks 6th highest salary<br> +<p>-- selects nth highest salary bellow ine picks 6th highest salary<br> select Top 1 sal from emp where sal in(Select Top 6 sal from emp order by desc) order by sal ASC</p> <p>select MIN(sal) from emp where sal in(Select Top 6 sal from emp order by desc)</p> <p>--We can get random records every time by using this query<br> select top 5 * from answer order by newid()<br> -==============================================================================================================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.</p> +=============================================================================================================</p> +<p>=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</p> +<p>also removes all of the rows from the result set that match the rows that +are on the right side of the EXCEPT operator.</p> <p>eg:</p> <p>SELECT C1,C2 FROM T1 EXCEPT SELECT C1,C2 FROM T2<br> SELECT C1,C2 FROM T2 EXCEPT SELECT C1,C2 FROM T1</p> <p>SELECT c1,c2 FROM t1 INTERSECT SELECT c1,c2 FROM t2</p> <p> -==============================================================================================================-- -selecting all column names from all tables</p> +=============================================================================================================</p> +<p>=-- selecting all column names from all tables</p> <p>Select TABLE_CATALOG = Left(TABLE_CATALOG, 10),<br> TABLE_SCHEMA = Left(TABLE_SCHEMA, 10),<br> TABLE_NAME = Left(TABLE_NAME, 10),<br> COLUMN_NAME = Left(COLUMN_NAME, 20)<br> From stars_db.Information_Schema.Columns<br> Where Table_Name like 'S000%'</p> -<p><br> -Select TABLE_CATALOG ,<br> +<p>Select TABLE_CATALOG ,<br> TABLE_SCHEMA,<br> TABLE_NAME ,<br> COLUMN_NAME<br> From stars_db.Information_Schema.Columns<br> Where Table_Name like 'S000%' AND COLUMN_NAME LIKE 'ValidC%'</p> <p> -==============================================================================================================select<br> - +=============================================================================================================</p> +<p>=select<br> 1 A<br> 2 B<br> 3 C</p> <p>like A,B,C........<br> declare @n varchar(1500)<br> set @n=''<br> select @n=@n + convert(varchar(10),QuestionID) + ',' from answer<br> select @n</p> <p>declare @str nVARCHAR(4000)<br> select @str = COALESCE(@str+',','Start ') + convert(varchar(10),QuestionID) from answer<br> select @str</p> <p> -==============================================================================================================-- -To get Table Created and modified date<br> +=============================================================================================================</p> +<p>=-- To get Table Created and modified date<br> Select name,create_date,modify_date From sys.tables</p> <p> -==============================================================================================================To -set Identity seed to any number use this following command</p> -<p>DBCC CHECKIDENT('tablename', RESEED, 10)</p> -<p>'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,...<br> -==============================================================================================================<br> - -<a href= +=============================================================================================================</p> +<p><a href= "http://sscheralbidar.blog.co.uk">http://sscheralbidar.blog.co.uk</a><br> <a href="http://sscheral.atwiki.com">http://sscheral.atwiki.com</a><br> <a href= -"http://sscheral.spaces.live.com/">http://sscheral.spaces.live.com/</a></p> +"http://sscheral.spaces.live.com/">http://sscheral.spaces.live.com/</a><br> +=============================================================================================================</p> +<p> </p> +<p>In-Built Functions<br> +AT(): Returns the beginning numeric position of the nth occurrence of a +character expression within another character expression, counting from</p> +<p>the leftmost character.<br> +RAT(): Returns the numeric position of the last (rightmost) occurrence of a +character string within another character string.<br> +OCCURS(): Returns the number of times a character expression occurs within +another character expression (including overlaps).<br> +OCCURS2(): Returns the number of times a character expression occurs within +another character expression (excluding overlaps). <br> +PADL(): Returns a string from an expression, padded with spaces or characters +to a specified length on the left side.<br> +PADR(): Returns a string from an expression, padded with spaces or characters +to a specified length on the right side.<br> +PADC(): Returns a string from an expression, padded with spaces or characters +to a specified length on the both sides.<br> +CHRTRAN(): Replaces each character in a character expression that matches a +character in a second character expression with the corresponding</p> +<p>character in a third character expression.<br> +STRTRAN(): Searches a character expression for occurrences of a second +character expression, and then replaces each occurrence with a third</p> +<p>character expression. Unlike a built-in function Replace, STRTRAN has three +additional parameters.<br> +STRFILTER(): Removes all characters from a string except those specified.<br> +GETWORDCOUNT(): Counts the words in a string.<br> +GETWORDNUM(): Returns a specified word from a string.<br> +GETALLWORDS(): Inserts the words from a string into the table.<br> +PROPER(): Returns from a character expression a string capitalized as +appropriate for proper names.<br> +RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right +search.<br> +ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified +numeric expression (from 1 to 3999).<br> +ROMANTOARAB(): Returns the number equivalent of a specified character Roman +numeral expression (from I to MMMCMXCIX).</p> +<p>AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL +INSTR, LPAD, RPAD, TRANSLATE, INITCAP.</p> +<p> +=============================================================================================================</p> +<p>=inbuilt functions</p> +<p>SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees;</p> +<p>SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName<br> +FROM Employees</p> +<p>SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees</p> +<p>SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS +PortionOfLastNameMirrorImage<br> +FROM Employees</p> +<p>SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle, +Title<br> +FROM Employees</p> +<p>SELECT REPLICATE('MyCoolString', 5)</p> +<p>SELECT SPACE(12) AS Spaces</p> +<p>SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, +(LEN(FirstName) - 1))) + ' '<br> ++ UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) - +1)))<br> +AS FullName<br> +FROM Employees</p> +<p>SELECT 'My Output' + CHAR(10) + CHAR(13)<br> ++ 'AnotherOutput'<br> +=============================================================================================================</p> +<p>=www.TransactSQL.Com</p>