+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Hidden Features of SQL Server (/Thread-Hidden-Features-of-SQL-Server)
Hidden Features of SQL Server - chivvied650523 - 07-31-2023
What are some hidden features of [SQL Server][1]?
For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough?
----------
## Answers ##
*Thanks to everybody for all the great answers!*
**Stored Procedures**
- **sp_msforeachtable:** Runs a command with '?' replaced with each table name (v6.5 and up)
- **sp_msforeachdb:** Runs a command with '?' replaced with each database name (v7 and up)
- **sp\_who2:** just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
- **sp_helptext:** If you want the code of a stored procedure, view & UDF
- **sp_tables:** return a list of all tables and views of database in scope.
- **sp_stored_procedures:** return a list of all stored procedures
- **xp_sscanf:** Reads data from the string into the argument locations specified by each format argument.
- **xp_fixeddrives:**: Find the fixed drive with largest free space
- **sp_help:** If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1
**Snippets**
- Returning rows in random order
- All database User Objects by Last Modified Date
- Return Date Only
- Find records which date falls somewhere inside the current week.
- Find records which date occurred last week.
- Returns the date for the beginning of the current week.
- Returns the date for the beginning of last week.
- See the text of a procedure that has been deployed to a server
- Drop all connections to the database
- Table Checksum
- Row Checksum
- Drop all the procedures in a database
- Re-map the login Ids correctly after restore
- Call Stored Procedures from an INSERT statement
- Find Procedures By Keyword
- Drop all the procedures in a database
- Query the transaction log for a database programmatically.
**Functions**
- HashBytes()
- EncryptByKey
- PIVOT command
**Misc**
- Connection String extras
- TableDiff.exe
- Triggers for Logon Events (New in Service Pack 2)
- Boosting performance with persisted-computed-columns (pcc).
- DEFAULT_SCHEMA setting in sys.database_principles
- Forced Parameterization
- Vardecimal Storage Format
- Figuring out the most popular queries in seconds
- Scalable Shared Databases
- Table/Stored Procedure Filter feature in SQL Management Studio
- Trace flags
- Number after a `GO` repeats the batch
- Security using schemas
- Encryption using built in encryption functions, views and base tables with triggers
[1]:
[To see links please register here]
RE: Hidden Features of SQL Server - cyrinexqaxpy - 07-31-2023
Here are some features I find useful but a lot of people don't seem to know about:
sp_tables
> Returns a list of objects that can be
> queried in the current environment.
> This means any object that can appear
> in a FROM clause, except synonym
> objects.
[Link][1]
sp_stored_procedures
> Returns a list of stored procedures in
> the current environment.
[Link][2]
[1]:
[To see links please register here]
[2]:[To see links please register here]
RE: Hidden Features of SQL Server - pize913533 - 07-31-2023
Simple encryption with [EncryptByKey][1]
[1]:
[To see links please register here]
RE: Hidden Features of SQL Server - catchier308184 - 07-31-2023
sp_who2, just like sp_who, but with a lot more info for troubleshooting blocks
RE: Hidden Features of SQL Server - Xantippe79855 - 07-31-2023
/* Find the fixed drive with largest free space, you can also copy files to estimate which disk is quickest */
EXEC master..xp_fixeddrives
/* Checking assumptions about a file before use or reference */
EXEC master..xp_fileexist 'C:\file_you_want_to_check'
[More details here][1]
[1]:
[To see links please register here]
RE: Hidden Features of SQL Server - meza862 - 07-31-2023
Connection String extras:
**MultipleActiveResultSets=true;**
This makes ADO.Net 2.0 and above read multiple, forward-only, read-only results sets on a single database connection, which can improve performance if you're doing a lot of reading. You can turn it on even if you're doing a mix of query types.
**Application Name=MyProgramName**
Now when you want to see a list of active connections by querying the sysprocesses table, your program's name will appear in the program_name column instead of ".Net SqlClient Data Provider"
RE: Hidden Features of SQL Server - shostakovich443 - 07-31-2023
A semi-hidden feature, the Table/Stored Procedure Filter feature can be really useful...
In the **SQL Server Management Studio** *Object Explorer*, right-click the **Tables** or **Stored Procedures** folder, select the **Filter** menu, then **Filter Settings**, and enter a partial name in the *Name contains* row.
Likewise, use **Remove Filter** to see all Tables/Stored Procedures again.
RE: Hidden Features of SQL Server - haland133 - 07-31-2023
Find records which date falls somewhere inside the current week.
where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )
Find records which date occurred last week.
where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
Returns the date for the beginning of the current week.
select dateadd( week, datediff( week, 0, getdate() ), 0 )
Returns the date for the beginning of last week.
select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
RE: Hidden Features of SQL Server - Mrdiscreetnesses375 - 07-31-2023
Drop all connections to the database:
Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
RE: Hidden Features of SQL Server - txplsiewfcr - 07-31-2023
Table Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
Row Checksum
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value