Oftentimes there is a need to retrieve different types of the same object (e.g. contacts). For example, in a Contacts database, you might have a Contact table containing many different types of contacts (employees, customers, suppliers, etc). Typically, a user might need to see a report of all different types of contacts for an order […]
2003-07-18
790 reads
This procedure allows you to search through all objects in all databases on your server for words/phrases in your object code. Very handy for cases in which, for example, a column name on a table has been changed and you need to search your entire server for any sprocs/views/etc. that might reference it.Syntax: [EXEC] sp_FindCodeStr […]
2003-06-10
261 reads
In your tenure as a DBA/developer, you've probably been asked to write dozens of stored procedures similar to this:CREATE PROCEDURE ContactInfo@ContactID intASSELECT c.Name, a.Address, p.PhoneFROM Contact cLEFT JOIN ContactAddress aON c.ContactID = a.ContactIDLEFT JOIN ContactPhone pON c.ContactID = p.ContactIDWHERE c.ContactID = @ContactIDBut there is a more efficient way to write such a query.....particularly if you […]
2003-06-03
1,417 reads
This procedure displays all user-defined datatypes in all databases on a server. Another example of the power of dynamic T-SQL, this procedure dynamically generates SELECT statements for the systypes tables in each database and UNIONs them together so that they display in one recordset.
2003-05-30
160 reads
Here's a real-life challenge I was faced with at work: my company (a talent agency) needed me to create a report listing all of our actors that we represent - and for each actor, a comma-delimited string of each production that they've starred in. Simple enough, right......except that, within the comma-delimited list of productions, they […]
2003-04-11
783 reads
This procedure, sp_AllInputBuffers, uses dynamic T-SQL to generate and execute the DBCC INPUTBUFFER statement for each server process (except yours). Perfect for performance troubleshooting situations, when you want to see what commands are being executed against your server. Also, a great example of the power of dynamic T-SQL.For the @exec parameter, pass 1 or leave […]
2003-04-04
654 reads
If, like me, you are constantly restoring/moving several databases between multiple environments (development to staging, production to training, etc.), you know how tedious it can get to use EM, or to manually type out all those RESTORE...WITH MOVE statements in QA.Even having a saved script isn't the ideal thing, as you still need to go […]
2003-02-10
1,794 reads
Regarding the recent script I submitted (dropping/recreating all procedures/views) - I made an important oversight. I neglected to add a CASE statement in order to make sure that the appropriate type of object was being referenced in the DROP statement.Below is the corrected script:
2003-02-06
211 reads
There are times when you may need to drop and re-create all stored procedures and/or views in your database. For example, in cases where procedures or views are causing blocked locks or other performance problems, a recent article (http://www.sswug.org/see.asp?s=1166&id=13448) suggested dropping/re-creating procedures and views after a service pack has been installed. The installation of a […]
2003-02-05
1,262 reads
This isn't a "script" per se - but rather an observation about a common issue in writing scripts.I want to alert SQL programmers to the issues surrounding the use of single-line comments. I try to avoid these whenever possible, and use block comments instead (except, of course, when commenting out the keyword GO).The reason is […]
2003-01-21
276 reads
Do you know if your SQL Server is really running at its best? To...
You can find the slides of my session on the €100 DWH in Azure...
By Steve Jones
This value is something that I still hear today: our best work is done...
Hi everyone I am writing an SP where there is logic inside the SP...
Comments posted to this topic are about the item Planning for tomorrow, today -...
We have a BI-application that connects to input tables on a SQL Server 2022...
I try to run this code on SQL Server 2022. All the objects exist in the database.
CREATE OR ALTER VIEW OrderShipping
AS
SELECT cl.CityNameID,
cl.CityName,
o.OrderID,
o.Customer,
o.OrderDate,
o.CustomerID,
o.cityId
FROM dbo.CityList AS cl
INNER JOIN dbo.[Order] AS o ON o.cityId = cl.CityNameID
GO
CREATE OR ALTER FUNCTION GetShipCityForOrder
(
@OrderID INT
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @city VARCHAR(50);
SELECT @city = os.CityName
FROM dbo.OrderShipping AS os
WHERE os.OrderID = @OrderID;
RETURN @city;
END;
go
What is the result? See possible answers