Posts Tagged SQL server
WHILE EXISTS ( SELECT * FROM table WHERE <condition to delete>)
BEGIN
SET ROWCOUNT 1000
DELETE Table WHERE <Condition>
SET ROWCOUNT 0
ENd
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
SQL Server Date formats
Jan 6
Date formats in sql server
DATE FORMATS
Format #
Query (current date: 12/30/2006)
Sample
1
select convert(varchar, getdate(), 1)
12/30/06
2
select convert(varchar, getdate(), 2)
06.12.30
3
select convert(varchar, getdate(), 3)
30/12/06
4
select convert(varchar, getdate(), 4)
30.12.06
5
select convert(varchar, getdate(), 5)
30-12-06
6
select convert(varchar, Read More
SQL server and code names
Nov 10
In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.
1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 †Read More
To connect sql management studio to any sql server using non defualt port, we have to use “,” beside the server IP (or host name).
For e.g., server: 1.1.1.1 and port 1234 we specify it as
1.1.1.,1234
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
USE AdventureWorks
GO
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
Delete SQL Log file
May 11
Sometimes, we just do not need the big log file. For example, I have a 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is:
Detach the database
Rename the log file
Attach the database without the log file
Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> T Read More
The following SQL query returns the size of each table in the specified database.
USE
sp_MSforeachtable @command1=’EXEC sp_spaceused ”?”’,@whereand=’or OBJECTPROPERTY(o.id, N”IsSystemTable”) = 1′
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
While browsing internet for searching string across database, I found the following stored procedure
EXEC SearchAllTables ‘Computer’
GO
Here is the complete stored procedure code:
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
– Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
– Purpose: To search all columns of all tables for a given search string
R Read More
To find out the SQL server version using SQL query:
<code>
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
</code>
Bookmark on DeliciousDigg this post Recommend on FacebookShare with StumblersTweet about itSubscribe to the comments on this post Read More
