Feeds:
Posts
Comments

Posts Tagged ‘undocumented SQL’

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

Posted in SQL, SQL Authority, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged on August 5, 2009 | 10 Comments »

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 optimization project, I have to decide [...]

Read Full Post »

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

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on April 26, 2009 | 7 Comments »

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 readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)

Read Full Post »

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged on May 5, 2008 | 7 Comments »

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 TABLE #TableNameResults (DatabaseName VARCHAR(100) NOT NULL, TableName VARCHAR(100) NOT [...]

Read Full Post »

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged on September 23, 2007 | 5 Comments »

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 and provides accurate information.
Sytanx:
EXEC sp_MShasdbaccess
GO

ResultSet: [...]

Read Full Post »

SQL SERVER – DBCC commands List – documented and undocumented

Posted in SQL, SQL Authority, SQL Documentation, SQL Query, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology, tagged on May 15, 2007 | 14 Comments »

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 analyzer.
DBCC TRACEON(2520)
DBCC HELP (‘?’)
GO
To learn about [...]

Read Full Post »

SQL SERVER – 2005 – List all the database

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged on May 12, 2007 | 24 Comments »

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 in SQL Server 2005
SELECT name
FROM sys.databases
SELECT name
FROM sys.sysdatabases
—-SQL SERVER Un-Documented [...]

Read Full Post »