CHAR can be used to insert control characters into character strings. The frequently used control characters are: CHAR(9) = Tab CHAR(10) = Line Feed CHAR(13) = Carriage Return My problem was trying to export data in Management Studio from a 3rd party application database into a spreadsheet. I found knowing this useful when a column […]
Category: SQL
Categories
TOP 100 PERCENT and ORDER BY
In SQL Server 2008, the TOP 100 PERCENT and ORDER BY is ignored. In SQL Server 2005, the output order isn’t guaranteed. For example if you created a view like so: CREATE VIEW vwProduct AS SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl ORDER BY lProductID DESC GO And tried to select from […]
USE Database –Change to the database you want to use SELECT name, create_date, modify_date FROM sys.objects –User defined objects system view WHERE type = ‘P’ –Only return stored procedures AND modify_date > ‘2009-09-01’ –If you aren’t looking for a specific data range, take this line out ORDER BY modify_date DESC Read sys.objects documentation Also read […]
Categories
Oracle – Start With…Connect By
http://halisway.blogspot.com/2006/04/sql-connect-by.html http://www.java2s.com/Code/Oracle/Hierarchical-Query/HierarchicalQueriesSTARTWITHandCONNECTBYPRIORclauses.htm
Categories
sp_rename
Changes the name of a user-created object (i.e. table, column, or user-defined data type) in the current database. Rename a table This example renames the customers table to custs. EXEC sp_rename ‘customers’, ‘custs’ Rename a column This example renames the contact title column in the customers table to title. EXEC sp_rename ‘customers.[contact title]’, ‘title’, ‘COLUMN’
The cause of this problem is the SQL server has been configured to operate in “Windows Authentication Mode (Windows Authentication)” and doesn’t allow the use of SQL accounts. This was the default setting in my case, I got the error after installing a new instance of SQL Server 2005. I was able to solve this […]
Categories
Getting Database Stats
You could use sp_helpdb [ [ @dbname= ] ‘name’ ]
Categories
sp_change_users_login (Transact-SQL)
Syntax sp_change_users_login [@Action =] ‘action’ [,[@UserNamePattern =] ‘user’] [,[@LoginName =] ‘login’] Arguments [@Action =] ‘action’ Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values. Auto_Fix Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It […]
Categories
Inner Join On Two SQL Databases
SELECT t1.columnName, t2.columnName FROM databaseName.user.table AS t1 INNER JOIN databaseName.user.table AS t2 ON t1.id = t2.id
INSERT INTO MyTable (FirstCol, SecondCol) SELECT ‘First’, 1 UNION ALL SELECT ‘Second’, 2 UNION ALL SELECT ‘Third’, 3