Getting the Paths for All Database Files in a SQL Server Instance

Here’s a quick T-SQL tip on getting the paths for all database files in a SQL Server instance.   The T-SQL Tip You can get the list of paths for all database files (both data and log files) using the undocumented SQL Server stored procedure sp_MSforeachdb. You can use the below T-SQL statement: exec sp_MSforeachdb @command1=”USE … Read more…

DBStats: A Stored Procedure for Easily Retrieving Basic DB Information

In this article, I am publishing a stored procedure (DBStats) I have recently written and which given a database name as an input parameter it returns the below basic information: TotalTables TotalViews TotalSPs TotalFunctions TotalDMLTriggers TotalDBSize (MB) LogSize (MB) List of all tables with their number of columns   Sample Output of DBStats The below … Read more…

Listing all Tables of a Linked SQL Server Database

In this article, we will be discussing about, how you can easily list all tables of a linked SQL Server database. Introduction There are many scenarios in the DBMSs world where you might need to use a linked server between two different SQL Server instances in order to execute direct queries between them. However, the majority … Read more…

T-SQL Tip: Inserting Leading Characters to a String

This post, is actually a T-SQL tip, on how to easily insert leading characters to a string. The Scenario for Inserting Leading Characters Consider that  you have a fixed-length table column for which you want to add leading characters, so that all its records, values with the same number of characters. How to Insert Leading … Read more…

T-SQL Tip: Retrieving Security-Related Info for SQL Server Logins

—— Retrieves Security-Related Information— for all the SQL Server Logins—— SQL Server versions  supported: SQL Server 2005 or later—SELECT [name] as LoginName,LOGINPROPERTY ([name] , ‘DefaultDatabase’) as DefaultDatabase,LOGINPROPERTY ([name] , ‘DaysUntilExpiration’) as DaysUntilExpiration,(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsExpired’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsExpired,(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsLocked’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) … Read more…

T-SQL Tip: Getting the File Locations for all DBs in a SQL Server Instance

—— Dynamically builds  T-SQL statements for retrieving the file — locations for all the databases in the SQL Server Instance—— SQL Server versions  supported: SQL Server 2005 or later—SELECT ‘use ‘+ [name]+’; select ”’+[name]+”’ as DBName,cast ([name] as varchar(45)) as LogicalFileName,cast (filename as varchar(100)) as FileName from sysfiles;’ as SQLStatement FROM master.sys.databases Details: Just execute the statements generated … Read more…

T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances

If you are looking for a T-SQL tip, on how to create a simple linked server between SQL Server instances, then the below tip might come in handy.   T-SQL Example for Creating the Linked Server The below T-SQL code does just that: — –Note: The Entire Operation Takes Place on the Local Instance — … Read more…

Getting the Disk Usage Statistics for all Tables in a Database

I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? 🙂   Get the disk usage statistics using the undocumented … Read more…

How To Get Basic SQL Server Instance Info (T-SQL Script)

With the below T-SQL script, you can get basic SQL Server instance info such as: Full instance name SQL Server version Edition Collation Number of databases Product level (i.e. SP-level) …and more   The T-SQL Script The script uses the built-in SQL Server function SERVERPROPERTY. SELECT SERVERPROPERTY(‘ServerName’) AS FullInstanceName, REPLACE(SUBSTRING(@@version,0,CHARINDEX(‘-‘,@@version)),’Microsoft ‘,”) as FullSQLVersion, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) … Read more…

T-SQL Tip: Retrieving Database File Sizes

The following script generates T-SQL statements that when ran, they return file size information for all the user databases on a SQL Server instance. –Script that generates T-SQL providing size information for all database files on a SQL Server InstanceSELECT ‘SELECT ”’+[name]+”’ as DBName,cast(f.name as varchar(25)) as DBFileName,f.Filename as PhysicalFileName,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB … Read more…

New Post Series: T-SQL Tips

Hello friends! I hope you are all having a great summer time! I am having a great summer time too, however I felt the need for some summer-blogging and so here I am, writing another article! 🙂 I will start this article with a simple question: How many times we all search the Web for … Read more…

How to retrieve size information for all the tables in a SQL Server Database

In an older post, we have discussed about, how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure “sp_MSforeachtable”.   Retrieving Table Size Info using sp_MSforeachtable Another common task is when you want to retrieve size information for all the tables in a database. … Read more…