Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

33 total

On This Page:

  1. 1 Bulk convert InnoDB tables to MyISAM
  2. 2 Delete top 1 rows
  3. 3 Reindex and rebuild statistics for entire database
  4. 4 Fix orphaned users in MSSQL
  5. 5 Reset identity seed for a table
  1. 6 Selecting different parts of a DATETIME with MSSQL
  2. 7 Delete duplicate rows from a table
  3. 8 Limit equivelent for MSSQL
  4. 9 SQL Update with Values from Second Table
  5. 10 Getting column name

Bulk convert InnoDB tables to MyISAM

to mysql sql innodb myisam convert by jamiew on Apr 02, 2008 1 Comments
// produces some handy ALTER statements
// I think there's a way to this w/ mysqladmin but I couldn't find it.

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB';

Delete top 1 rows

to sql by taurin on Mar 07, 2008
// Deletes the top 1 row from the table

set rowcount 1
DELETE FROM xxx WHERE xxx
set rowcount 0

Reindex and rebuild statistics for entire database

to sql mssql by taurin on Mar 07, 2008
// Reindex and rebuild statistics for each table in the database

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

Fix orphaned users in MSSQL

to users sql mssql microsoft data transact orphan orphaned user restore restoration restoring by chrelad on Dec 11, 2007
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'

Reset identity seed for a table

to sql reset microsoft column transact reseed seed identity ident field ms dbcc checkident by chrelad on Nov 16, 2007
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) ;

Selecting different parts of a DATETIME with MSSQL

to time date sql timestamp mssql language query transact structured datetime parts part stamp only sub by chrelad on Nov 14, 2007
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

Delete duplicate rows from a table

to mysql sql database db by jamiew on Nov 06, 2007
// 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;

Limit equivelent for MSSQL

to mysql sql mssql limit clause paging pagination paginating offset limitting by chrelad on Oct 12, 2007
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)."
_

SQL Update with Values from Second Table

to sql update by ngwells on Oct 09, 2007
// 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

Getting column name

to sql by baburk on Sep 19, 2007 1 Comments
// 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.
33 total

On This Page:

  1. 1 Bulk convert InnoDB tables to MyISAM
  2. 2 Delete top 1 rows
  3. 3 Reindex and rebuild statistics for entire database
  4. 4 Fix orphaned users in MSSQL
  5. 5 Reset identity seed for a table
  1. 6 Selecting different parts of a DATETIME with MSSQL
  2. 7 Delete duplicate rows from a table
  3. 8 Limit equivelent for MSSQL
  4. 9 SQL Update with Values from Second Table
  5. 10 Getting column name