DataBase, PowerShell

PowerShell Script to Query Specified Columns of a .MDB DataBase

Script connects to the .MDB file using user DSN named “MS Access Database” pointing to the .MDB file and then queries the specified columns. [code language=”powershell”] $adOpenStatic = 3 $adLockOptimistic = 3 $userName = $env:USERNAME $newDbPath = "C:\Temp\$userName\DPW" $connectionString = "DSN=MS Access Database;" $sourceQuery = "Select * From Connection" $objConnection = New-Object -com "ADODB.Connection" $objRecordSet = New-Object -com "ADODB.Recordset" $objConnection.Open($connectionString) $objRecordset.Open($sourceQuery, $objConnection, $adOpenStatic, $adLockOptimistic) $objRecordset.MoveFirst() While ($objRecordset.EOF -ne $True) { ForEach ($column in "ConnectString", "FilePath", "BackupPath") { $objRecordset.Fields.Item("$column").Value } $objRecordset.MoveNext() } $objRecordset.Close() $objConnection.Close() [/code] .

Read more
DataBase, Scripting

SQL Script to list all DBs Sizes in MBs

SQL Script to list all DBs Sizes in MBs [code language=”sql”] SELECT DbName ,DbState ,DbRecovery ,[ROWS], [LOG], [FILESTREAM], [FULLTEXT] FROM ( SELECT DB.name AS DbName ,DB.state_desc AS DbState ,DB.recovery_model_desc AS DBRecovery ,MF.type_desc AS FileType ,CONVERT(int, ROUND(MF.size * 0.0078125, 0)) AS SizeMB FROM sys.databases AS DB INNER JOIN sys.master_files AS MF ON DB.database_id = MF.database_id WHERE HAS_DBACCESS(DB.name) = 1 ) AS DBS PIVOT (SUM(SizeMB) FOR FileType IN ([ROWS], [LOG], [FILESTREAM], [FULLTEXT]) ) AS PVT ORDER BY DbName [/code]

Read more
DataBase, Scripting

SQL Stored Procedure to search a complete SQL DB for a string/keyword

Create below SearchSQLDB Stored Procedure by connecting to the target SQL DB in your SQL MGMT studio. [code language=”sql”] CREATE PROC SeachSQlDB ( @SearchPattern nvarchar(100) ) AS BEGIN CREATE TABLE #TempResults (ColumnName nvarchar(300), ColumnValue nvarchar(3000)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @TempSrchStr nvarchar(110) SET @TableName = ” SET @TempSrchStr = QUOTENAME(‘%’ + @SearchPattern + ‘%’,””) WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = ” SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)), ‘IsMSShipped’) = […]

Read more