Refresh All Views on SQL Server

Whenever you make a change to a table - say, to modify a column - you need to update all the views that depend on that column.  This script will find and update all your views. 




-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(
    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''',0
    FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    WHERE so.type = 'V'
    and is_schema_bound_reference = 0
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp




Of course if a view is now no longer correct, you'll see on your messages tab in SQL Server Management Studio.




...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Selecting scalar values from a stored procedure

The easy and obvious answer is to use a function, not a procedure.   Then you can use it like any built-in T-SQL function.




select 1, dbo.Function()

But alas, functions do not allow us to store (insert, update, delete) any data to the database and my stored procedure needs to do exactly that. 



In a moment of brilliant engineering, SQL Server does not allow the return value of a stored procedure to appear as a column in a query (select, insert, update, where clause),  so you can't just say...




select orderID, dbo.StoredProcedure() from...

In my task, I had to insert the results of a stored procedure into a column in a table.  The code example I show below creates a temporary lookup table, and uses it later for a list of inserts.  try to follow this...




--creates a temp table for later use in joining (not shown)
declare @PickListNo table(OrderID varchar(20), PickListNo int, done bit);

--loads the temp table, except the column from the stored procedure
insert into @PickListNo (OrderID, done)
(
    Select distinct AOO.[order number], 0 from uploads.open_orders AOO
)

--vars the stored procedure needs
declare @A int
declare @NextVal int   

--while there are unprocessed rows...
while exists (select 1 from @PickListNo where PickListNo is null)
BEGIN
    --execute the procedure and capture the return value
    exec @A=dbo.NextPickListNo @NextVal OUTPUT
   
    --update one row in the table
    --I used Max(ID) to find a single row, but I might have used MIN, or Select top 1 as well.
    update @PickListNo set PicklistNo = @A where OrderID=(select MAX(orderID) from @PickListNo where PickListNo is null)
END

This use of the while loop is my way of cheating and not using cursors.  Cursors are nicely powerful but demand a lot of babysitting and resources (so I am told).



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.