undocumented SQL Archives - SQL Authority with Pinal Dave

SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

August 5, 2009

SQL Scripts, SQL Stored Procedure, undocumented SQL

When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine. At one point in…

SQL SERVER – List All the Tables for All Databases Using System Tables

April 26, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL System Table, undocumented SQL

Today we will go over very simple script which will list all the tables for all the database. sp_msforeachdb 'select "?" AS db, * from [?].sys.tables' Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from…

SQL SERVER – Find Table in Every Database of SQL Server – Part 2 Extension

May 5, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, undocumented SQL

Long time blog reader and SQL Server Expert Simon Worth has suggested two additional method to achieve same results as described in article SQL SERVER – Find Table in Every Database of SQL Server. Method 1 sp_msforeachdb "SELECT '?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE '%address%'" Method 2 CREATE…

SQL SERVER – Stored Procedure to Know Database Access Permission to Current User

September 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts, SQL Stored Procedure, undocumented SQL

Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable…

SQL SERVER – DBCC commands List – documented and undocumented

May 15, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Server DBCC, undocumented SQL

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises. To learn about all the DBCC commands run following script in query…

SQL SERVER – 2005 – List all the database

May 12, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, undocumented SQL

List all the database on SQL Servers. All the following Stored Procedure list all the Databases on Server. I personally use EXEC sp_databases because it gives the same results as other but it is self explaining. ----SQL SERVER 2005 System Procedures EXEC sp_databases EXEC sp_helpdb ----SQL 2000 Method still works…