// Deletes the top 1 row from the table

set rowcount 1
DELETE FROM xxx WHERE xxx
set rowcount 0

// Reindex and rebuild statistics for each table in the database

Exec sp_MSforeachtable "dbcc dbreindex('?')"
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

This SQL query will reset an identity seed to the specified value.

Thanks to this site for the answer:

http://www.thescripts.com/forum/thread657059.html

I know this works for MSSQL 2000 at least :)

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Thanks to this website for the information:

http://www.databasejournal.com/features/mssql/article.php/1442021

-- The syntax for pulling a certain part of a DATETIME is:
--
-- CONVERT(date_type[(length)],expression[,style])
--
-- The available styles are as follows:
--
-- NULL  Jun 24 2001 9:48PM
-- 1     06/24/01
-- 101   06/24/2001
-- 2     01.06.24
-- 104   24.06.2001
-- 108   21:48:00
-- 112   20010624
-- 121   2001-06-24 21:48:00.000

-- Some example usage:
SELECT CONVERT(DATETIME,GETDATE(),112) as date
-- Will output in YYYYMMDD format
SELECT CONVERT(DATETIME,client.birthday,101) as birthday
-- Will output in MM/DD/YYYY format

// Whoops, didja forgot to add a unique index to that column? No problem

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

Props to this site for the answer:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5

// MySQL limit clause
SELECT emp_id,lname,fname FROM employee LIMIT 20,10

// MSSQL equivelent
select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc

"The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30)."
_

// Update multiple fields in a table based on values in a second table. This prevents you from having to use multiple sub-queries.

UPDATE table
SET col1 = a.col1, col2=a.col2
FROM anotherTable a
WHERE a.anotherTableID = 1

// description of your code here

// insert code here..

I wants to get the number of column in a table and also every column name one by one both in sql and in T SQL.

Anybody help.

This SQL statement will alter a table only if a column in the table to be altered does NOT contain a column named 'FavoriteColorId'.

From: http://haacked.com/

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS)SQL (all I've tested it in).

SELECT * FROM information_schema.columns WHERE table_name = 'Customers'

Was importing a DB but wanted to prefix the tables, but the file was too large for TextMate. Here's all the vim substitutions

%s/DROP TABLE IF EXISTS `/DROP TABLE IF EXISTS `prefix_/g
%s/CREATE TABLE `/CREATE TABLE `prefix_/g
%s/INSERT INTO `/INSERT INTO `prefix_/g
%s/LOCK TABLES `/LOCK TABLES `prefix_/g
%s/ALTER TABLE `/ALTER TABLE `prefix_/g

delete from korovka_radacct WHERE "nasipaddress" = '81.4.195.254' AND "h323calltype" = 'VoIP';

Please note: "INSERT... ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...ON DUPLICATE KEY..."

Table logs:
id: INT(11) auto_increment primary key
site_id: INT(11)
time: DATE
hits: INT(11)

Then:

CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);

And then you can:

INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15;

Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.

from: http://dev.mysql.com/doc/refman/5.0/en/insert.html

// sample "update from" sql statement

UPDATE 
        [Crm].[tbPartyLegalEntityData]
SET 
        [CompanyType] = ape.[CompanyType],
        [EmployeesCount] = ape.[EmployeesCount]
FROM 
        [Crm].[tbPartyLegalEntityData] p
INNER JOIN 
        App.[tbApplicationPersonEmployer] AS ape 
ON 
        ape.[Bulstat] = p.[Bulstat]
WHERE 
        ape.[Bulstat] <> '!' 
        AND [ape].[EmployeesCount] IS NOT NULL 
        AND [ape].[CompanyType] IS NOT NULL

// takes a string and rips out the firstname and surname from it

select 
substring(login_id, 1, patindex('% %', login_id)) as firstname,
substring(login_id, patindex('% %', login_id), len(login_id)) as surname

from c_contact
and login_id <> ''

// count duplicate users and store in db

// count users with filter
select Count(surname+firstname) as duplicates, surname, firstname
from c_contact
where show_record=1
group by surname, firstname
having Count(surname+firstname) > 1
order by duplicates, surname, firstname

// original code
SELECT column,COUNT(*) c 
FROM table
GROUP BY column 
HAVING COUNT(*) > 1

//update code
update c_contact
set duplicates = dbo.func_get_duplicates(surname, firstname)

Truncates and shrinks log files for each user database on MS Sql server.

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'

exec sp_msforeachdb @ssql

This is a trigger to implement auto-increment/identity columns in Firebird. Another version is using a stored procedure to
get the value of the generator before inserting it then returning the saved generator value. This is needed because Firebird
generators are outside transaction control so reading the generator value again after using it may yield a different number
because it has already been used in a different transaction.

CREATE GENERATOR testgen;

SET TERM $$ ;
CREATE TRIGGER trg_gen_test FOR test_table
ACTIVE
BEFORE INSERT AS
BEGIN
  new.identity_column = gen_id(testgen, 1);
END $$
SET TERM ; $$


SET TERM $$ ;

CREATE PROCEDURE proc_insert ( val1 VARCHAR(20), val2 VARCHAR(20) ) RETURNS 
(generated_id INTEGER)
AS
BEGIN
  generated_id = gen_id(testgen, 1);
  INSERT INTO test_table (identity_column, col1, col2) VALUES (:generated_id, :val1, :val2);
  SUSPEND;
END $$

SET TERM ; $$

/* call the above procedure like this from code (PHP):
SELECT generated_id FROM proc_insert('a', 'b');
of course you can prepare and use placeholders/bind params for the proc parameters
*/

// remember to check that the path is correct.
// This is running on a dedicated server on TextDrive

#!/bin/sh

# This file will run a backup of your desired MySQL database and
# remove any backups older than 7 days.
#
# If youOd like to preserve backups for longer than a week, like say 
# 2 weeks, then set the '-mtime' value from '+7' to '+14'.
#

TIME_STAMP=`date "+%Y-%m-%d"`
echo "starting "$0" on" `date`
for db in db1 db2 db3
do
  DB_STAMP=${db}_${TIME_STAMP}
  echo ${DB_STAMP}
  /opt/csw/mysql5/bin/mysqldump --opt --skip-add-locks --user=username --password=password ${db} | gzip > /domains/backups/mysql/${DB_STAMP}.gz
  /opt/csw/bin/mutt -s "mysql  ${TIME_STAMP}"  -a /domains/backups/mysql/${DB_STAMP}.gz someuser@somedomain.com dev/null
done

cd /domains/backups/mysql/

/usr/bin/find *.gz -mtime +14 -exec rm {} \;
echo "finished "$0" on" `date`