Tag Archives: SQL

SQL PowerShell to check and repair Ad-hoc distributed queries Vulnerability

Introduction Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. As the name suggests, ad-hoc distributed queries should only be used with infrequent access to data sources. For the … Continue reading

Posted in PowerShell, Security, SQL, T-SQL, Uncategorized, Vulnerability | Tagged , , , , , | Leave a comment

Recover Deleted Data from SQL Table Using Transaction Log and LSNs

Introduction At times a user may perform UPDATE operation or DELETE operation in SQL server database without applying the WHERE condition. This is a very common reason for encountering loss of data from SQL Server Tables. As the SQL Server … Continue reading

Posted in SQL, SQL 2016, SQL Server 2017 | Tagged , , , | 1 Comment

T-SQL :- How to Search String in all Stored Procedures across All User Defined Databases

There are many different ways to accomplish this tasks. Download Link https://gallery.technet.microsoft.com/T-SQL-How-to-Search-String-a1704fc6 The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string CREATE TABLE #ProcSearch (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate … Continue reading

Posted in SQL, T-SQL | Tagged , , , , , | Leave a comment

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and … Continue reading

Posted in sp_MSforeachtable, SQL, T-SQL | Tagged , , , , , , , , , , | 1 Comment

T-SQL – How to find Next Business day from a given date

There are many methods to find next business day. One common way is to use of calendar table.  This post is to get an output using T-SQL Or UDF function. Download T-SQL  NextBusinessDay The details are given below The script can be executed by … Continue reading

Posted in T-SQL | Tagged , , , , , , , | 1 Comment

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Inventory Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When … Continue reading

Posted in PowerShell, SQL, SSAS, SSRS, T-SQL | Tagged , , , , , , , | 2 Comments

The scripting variables can be defined explicitly by using the setvar command or implicitly by using the sqlcmd -v option. Also, this is very helpful in Replication to check rowcount across publisher and subscriber articles. Explicit Call- SQLCMD can be … Continue reading

Posted on by Prashanth Jayaram | Leave a comment