Find Largest table by Row count – SQL server 2005

May 31

adminUncategorized No Comments

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


Delete SQL Log file

May 11

adminUncategorized , No Comments

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-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file

Extracted from http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx

Getting tables size SQL server

May 07

adminUncategorized No Comments

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′

Search all tables in SQL Server

Mar 17

adminUncategorized No Comments

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
– Written by: Narayana Vyas Kondreddi
– Site: http://vyaskn.tripod.com
– Tested on: SQL Server 7.0 and SQL Server 2000
– Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ”
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Above information was extracted from http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Identifying SQL server version using SQL

Feb 09

adminSQL server No Comments

To find out the SQL server version using SQL query:

<code>

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

</code>