DZone Snippets: mssql code

Select DataBase Schema

dubby (Dave) — Wed, 17 Oct 2007 11:30:21 GMT

// Select database schema.
//This could be used to recreate or test for existence of columns/tables
//or could also be used to create a database template system to enable the writing //of database schema into txt template file to be recreated again by reading the //txt file via an application
//
//You can also use SELECT * instead of defining each schema property (column)



SELECT TABLE_CATALOG

, TABLE_SCHEMA

, TABLE_NAME

, ORDINAL_POSITION

, COLUMN_DEFAULT

, IS_NULLABLE

, DATA_TYPE

, CHARACTER_MAXIMUM_LENGTH

, COLLATION_NAME 

FROM 

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (N'Persons')

MSSQL 2005 - Add ID value to ID column when INSERTING

dubby (Dave) — Tue, 11 Sep 2007 07:50:29 GMT

// @TableName is obviously the TABLE name u use
// @ColumnName is obviously the COLUMN name u use



---Get next ID number

	DECLARE 

		@ID int

	

	IF (SELECT count(*) FROM @TableName ) > 0

		BEGIN

			SELECT @ID  = max(ColumnName ) from @TableName

			SET @ID = @ID + 1 

		END

	ELSE

	BEGIN

		SET @ID  = 1

	END

SQL SERVER: Delete Duplicate Rows with Primary Id

cornerblue (CornerBLUE, Inc.) — Fri, 31 Aug 2007 19:12:09 GMT

Deletes duplicates (leaving one instance) where the table has a primary key. Good for tables with Id, DupColumn, DupColumn...

(This is MS-SQL specific)



DELETE

FROM 	TableName

WHERE 	Id NOT IN

	(SELECT 	MAX(Id)

        FROM   		TableName

        GROUP BY 	DuplicateColumName1, DuplicateColumName2)

Configure MSSQL Linked Server to DB2 (via ODBC System DSN)

wjchay (Chay Weei Jye) — Mon, 26 Mar 2007 08:49:53 GMT

1. Install DB2 on MSSQL machine
2. Start Configuration Assistant. Add a new database mapping to the desired target DB2 database. Select option to create a System DSN along the way.
3. Start Microsoft’s ODBC Data Source Administrator. There should be a System DSN created from the previous step. Configure it with the userid/password for the target DB2 database.
4. Create linked server in MSSQL: EXEC sp_addlinkedserver @server = 'TMON', @srvproduct = '', @provider = 'MSDASQL', @datasrc = 'TMON'
5. Map access to linked server: EXEC sp_addlinkedsrvlogin 'TMON', 'false', NULL, 'db2admin', 'db2admin'
6. 2 ways to test the link: SELECT * FROM TMON..DB2ADMIN.USERS -- use uppercase for server, schema, table names SELECT * from OPENQUERY (TMON,'select * from users')

Notes:
* TMON is the remote DB2 database, also used as the DSN name.
* Remote DB2 server uses access id: db2admin and password: db2admin
* USERS is a table in the remote DB2 database

Setup for Performance Testing: Clear cache, buffers (MSSQL)

MattScilipoti (Matt Scilipoti) — Thu, 08 Dec 2005 03:33:32 GMT

For Microsoft SQL (MSSQL).
Use this to clear the cache and buffers to ensure comparison are accurate.



dbcc freeproccache

go

dbcc dropcleanbuffers

go