T-SQL Tips

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

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

Here’s a quick 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 [?]; SELECT CAST(DB_NAME() AS VARCHAR(50)) AS DBName, CAST(physical_name AS VARCHAR(150)) as…

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…

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…

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

—–The Entire Operation Takes Place on the Local Instance— USE [master]GO–Create the linked server objectEXEC master.dbo.sp_addlinkedserver @server = N'[LinkedServerName]’, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N'[DestSQLInstanceName]’ –Set up the user mapping between local and remote instancesEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[LinkedServerName]’,@useself=N’False’,@locallogin=N'[LocalLogin_SQL_or_Windows]’,@rmtuser=N'[Remote_SQL_Login_Name]’,@rmtpassword='[Remote_SQL_Login_Password]’GO –Example of querying a remote table select * from [LinkedServerName].[Database_Name].[Schema_Name].[Table_Name] For more info, check out the following links: sp_addlinkedserver Linked Servers Theory

T-SQL Tip: Retrieving SQL Server Instance-Related Information

The following T-SQL script returns the following instance-related information for a SQL Server installation: Server Domain Server Name SQL Server Instance Version SQL Server Instance Product Level (i.e. Service Pack #) SQL Server Instance Edition  SQL Server Instance Name —–Returns SQL Server Instance-Related Information—SELECTDEFAULT_DOMAIN() AS Domain,SERVERPROPERTY(‘MachineName’) AS ServerName,    …

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…