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!)

About this user

Christopher Dale

8 total

On This Page:

  1. 1 iSQL sucks, so make it work better
  2. 2 Case sensitivity in PostgreSQL
  3. 3 Fix orphaned users in MSSQL
  4. 4 Reset identity seed for a table
  5. 5 Selecting different parts of a DATETIME with MSSQL
  1. 6 Limit equivelent for MSSQL
  2. 7 Scripting schema updates in MSSQL #1
  3. 8 Retrieve MSSQL table information

iSQL sucks, so make it work better

to sql unix linux tsql language query transact structured odbc isql unixodbc by chrelad on Oct 28, 2008
Runs the SQL code located in a file you pass as a parameter against the DSN you specify as a second parameter.

What does it need?:
-----------------------------------
* isql (if you're reading this, you should have this)
* A pager (less/more/most/etc, you should have this)
* bash (you can try it in your shell, but no guarantees)
* tr (you should have this)
* sed (you should have this)

The code:
-----------------------------------
#!/bin/bash
# {{{ sql $1 $2 - Run a SQL script
function sql
{
      tr '\n' ' ' < "$1" | tr '\t' ' ' | isql "$2" Username Password | sed '1,8d' | sed 's/^.*SQL> //g' | $PAGER
}
# }}}


Parameters:
-----------------------------------
$1 = The file that contains SQL
$2 = The DSN you want to run the SQL against


What does it do?:
-----------------------------------
It chops off the extraneous "thing" that is spit out whenever you start iSQL, it looks like this:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>


and then it pipes everything into your specified pager ($PAGER) so you can at least scroll around in the results instead of seeing an ASCII art wrapped mess of your results.

Example use:
-----------------------------------
username@host $> sql ~/sqlscripts/getallusers.sql DSNName


Notes:
-----------------------------------
For my purposes I use the same credentials for all my databases (so I don't forget them) which is why they are hardcoded in the function and not passed into the function.

By the way, if anyone has any time on there hands, could you please pick up the slack on the development of this program. It's about the only CLI tool (that I know of) that can work with TSQL on a MSSQL 2000 server. Or, if someone knows of something better, let me know!

Thanks:
-----------------------------------
Thanks everyone, and I hope others find this somewhat useful (although I know it's a far cry from perfect),

Chrelad

Case sensitivity in PostgreSQL

to databases sql select database tables table column columns query postgresql postgre psql rdbm names name lower upper case lowercase uppercase statement by chrelad on Sep 18, 2008
When I started using PostgreSQL, it seemed odd that when I created a table called Users with column names like userId, userPassword, etc. When I would select these items from the table, the column and table names would all be in lowercase. It's quite simple really, I wasn't using quotes :) Below is an example of how to maintain your case in PostgreSQL:

This will all come out lowercase
create table Users (
  userId integer unique not null,
  userFirstName varchar(50) not null,
  userLastName varchar(50) not null
);


Whereas this will come out as you expect it to
create table "Users" (
  "userId" integer unique not null,
  "userFirstName" varchar(50) not null,
  "userLastName" varchar(50) not null
);

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

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)."
_

Scripting schema updates in MSSQL #1

to sql tsql database mssql schema update tables microsoft columns query scripting information_schema alter altering by chrelad on Aug 08, 2007 1 Comments
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 MSSQL table information

to sql server tsql mssql table microsoft column information properties sysproperties systables data type types length columns by chrelad on Aug 08, 2007
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'
8 total

On This Page:

  1. 1 iSQL sucks, so make it work better
  2. 2 Case sensitivity in PostgreSQL
  3. 3 Fix orphaned users in MSSQL
  4. 4 Reset identity seed for a table
  5. 5 Selecting different parts of a DATETIME with MSSQL
  1. 6 Limit equivelent for MSSQL
  2. 7 Scripting schema updates in MSSQL #1
  3. 8 Retrieve MSSQL table information