Get rowcount for all tables in a database

This one a simple post, we are getting the rowcount for each table in the current database. That’s it.

--Get the rowcount for each table in the current database
IF OBJECT_ID('tempdb.dbo.#tcounts', 'U') IS NOT NULL
 DROP TABLE #tcounts; 
CREATE TABLE #tcounts (
 Table_Name nvarchar(255),
 Row_Count int
)
EXEC sp_MSForEachTable @command1='INSERT #tcounts (Table_Name, Row_Count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #tcounts ORDER BY Table_Name
IF OBJECT_ID('tempdb.dbo.#tcounts', 'U') IS NOT NULL
 DROP TABLE #tcounts;

 

SQL Server Backup and Restore from Network Drives

Working with virtual machine environments has always presented a challenge in figuring out how to “easily” or “effectively” restore SQL Server databases. This is especially true during migrations and upgrades. It is easy to run out of hard drive space when copying, moving and manipulating several terabytes of content databases. Recently, I found myself working with tens of terabytes and I found myself lacking in space and time. Even with enough storage, copying and moving the databases was taking significant amounts of time. I concluded that it would be to my benefit, my client’s benefit and all of those great people reading this blog to figure out how to backup and restore a SQL database from a network drive. And, I figured it out after putting together several scripts and testing various credentials on the network.  Yes, you heard right, it exists. The following script effectively maps a network drive under the SQL Server process account which also performs the backups and restores. This is huge: its a huge storage saver, but more importantly and huge, huge time saver. Alas, here’s the script (in three steps):

Step 1: Map the drive

In this step, we issue the commands to map a drive (i.e “U:”) under the SQL Server process account with an account that has access. Replace the “DOMAIN\username” with your account if you have access to the network share to map the network drive under your credentials.  By running the command in the Query Window, the SQL Server process account will be able to see the mapped drive, thus, allowing us to backup and restore to that network drive directly.

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
EXEC XP_CMDSHELL 'net use u: \\networkserver\ShareBackups MySecretPassword /USER:DOMAIN\username'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Step 2: Backup or Restore

In this step, we backup or restore the desired databases.

To perform a backup (note we use copy_only to avoid breaking the backup chain and compression):

BACKUP DATABASE WSS_CONTENT TO DISK = 'u:\WSS_CONTENT.bak' WITH COPY_ONLY,COMPRESSION;
GO

To perform a restore:

RESTORE DATABASE WSS_CONTENT FROM DISK = 'U:\WSS_CONTENT.bak' WITH MOVE 'WSS_CONTENT' TO 'D:\SQLData\WSS_CONTENT_Data.mdf', MOVE 'WSS_CONTENT_Log' TO 'D:\SQLLogs\WSS_CONTENT_Log.ldf', RECOVERY, REPLACE, STATS = 10;
Step 3: Unmap the drive

Lastly, we issue the commands to delete the mapped drive (i.e “U:”).

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

 

Finding duplicate values in a SQL table

Even though this is simple, I keep needing this every once in a while. This is for SQL Server.

To Find the list of fields for which there are duplicates (or multiple records), use:

SELECT [field1],[field2],[field3], count(*) as DUPE_COUNT INTO #DupeList FROM table_name GROUP BY [field1],[field2],[field3] HAVING count(*) > 1

Now to see the dupes:

SELECT * FROM #DupeList

Now, I prepare my delete … and verify I see only what I want to clean up:

SELECT * FROM table_name
WHERE [field1] in (SELECT [field1] FROM #DupeList)
ORDER BY [field1],[field2][field3]

Here we delete the dupes … be sure the query matches the select query to not delete unexpected data/values: (I commented this deliberated to avoid accidents)

–Clean up duplicate
–DELETE FROM table_name WHERE [field1] in (SELECT [field1] FROM #DupeList)

And we always clean up temp tables:

–Clean up the temp table
DROP TABLE #DupeList

For more details:
http://support.microsoft.com/kb/139444