Script to drop all indexes on a table
Given a table name as the parameter, all the indexes on the table can be dropped. This script can be useful if you want to drop indexes on a table inside another procedure
2002-03-12
13,915 reads
Scripts
Script to export Stored Procedures
This procedure when executed with the correct parameters, exports all stored procedures from a database into different .SQL files in the path specified.Usage :exec USP_PRINT_PROCS username, password, servername, databasename, directory path
2002-03-12
573 reads
User Tables and their row count
Returns the Owner, Table Name, Row count for all user tables in the current database.
2002-03-11
627 reads
Check for orphaned user or sid in syslogins
When rebuilding a server or setting up a backup or DR server the logins need to be added into master and synchronised with the user databases.Inevitably there are orphaned users is sysusers or the sid's do not match with syslogins.I use this stored procedure to check for a particular user when the user experiences a […]
2002-03-07
824 reads
Deletes all Constraints from Table
--Delete all restrictions from those tables included in the input parameters Procedure HP_BORRA_CONSTRAINTS -- Include CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints.
2002-03-07
478 reads
Pivot Data based on Unknown Field Items
I was posed a question a few months back on another site about being about to create a Pivot Table (Horizontal version of vertical data) and I saw the question again today. This will allow you to do that, just make changes to the query where the title is the replacement need. It will build […]
2002-03-07
1,338 reads
Rows count for each and every table in a DB
By using this script one can find number of rows for each and every table in a database. Just cut and past this script in your DB and excute. A stored procedure will be created as 'Get_rowcounts_for_eachtable'. If U excuted this stored procedure, U can get all tablenames and rowcounts for respected […]
2002-03-07
584 reads
sp_GetBackupInfo
This will report all backup activity for all databases, sorted by date, so you see the last backup activity first. You can filter this one by a db name as well, and only see the backup info for said database.
2002-03-07
812 reads
sp_GetLastBackup
This will get the last date a database was backuped. It allows you to report this date on all databases on a server, or pass in a db name, and it'll report the date that db was last backed up
2002-03-07
455 reads
Populate a table with a directory's file info.
I use this SP to return full directory information into a table. FilePath, FileName, Filedate and FileSize are parsed from a 'dir /n/on/-c ' command. FilesInDir is the table created. usage: exec LoadFileDetailsIntoTable 'c:\winnt'select * from FilesInDir where filesize > 100000select min(fileDate) from FilesInDirThe Return Code will equal the number of files found.
2002-03-05
482 reads
Blogs
Excellent article on securing msdb
By Brian Kelley
Fabiano Amorim has written an excellent article at Simple Talk on securing msdb. Here's...
T-SQL Tuesday #201: Temp Tables
By Steve Jones
This month we have a new host, which I am grateful for. So many...
When Do You Reach For A #temp Table?
I needed to do some research because there is more to #temp tables than...
Forums
Return all intermediate members of a recursive CTE?
I wrote a recursive CTE to determine employee levels... but what if I wanted...
jdbc auth failing to sql from sap's cloud middleware CPI
hi , our business partner is asking s4 sap api's for receivable info and...
Searching for my own posts
I have a really stupid question... Is there an easy way to search for...
Question of the Day
Escaping Strings
The STRING_ESCAPE() function requires 2 parameters in SQL Server 2025. The first is a string of text and the second is the type of escape rules being used. What are the options for the second parameter?
See possible answers