Ottenere l’Url della pagina chiamante

Public Shared Function GetPaginaChiamante() As String

    Dim res As String = ""

    With System.Web.HttpContext.Current.Request
        If .UrlReferrer IsNot Nothing Then
            res = .UrlReferrer.ToString
        End If
    End With

    Return res

End Function

 

Esempio di utilizzo:

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

    Me.btnAnnulla.PostBackUrl = GetPaginaChiamante

End Sub

Nota: La funzione GetPaginaChiamante() è nella ITovaglieri.Web.UI.Domain! :)

Backup di tutti i db

EXEC sp_MSForEachDB  
'IF ''?'' NOT IN (''tempdb'') 
BEGIN
 PRINT ''''
 PRINT ''''
 DBCC checkdb (''?'')
 PRINT ''''
 PRINT ''''
 BACKUP DATABASE [?] TO  DISK = N''c:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\?.bak'' 
  WITH RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N''? - Completo Database Backup'', SKIP, 
  NOREWIND, NOUNLOAD,  STATS = 10
END'

Su SQL2000 non funziona :(

SQL Server Hidden Stored Procedures

Introduction

sp_msforeachdb and sp_msforeachtable are very powerful stored procedures. They allow you to loop through the databases and tables in your instance and run commands against them. I have used these extensively in my day to day work as a DBA. Both of the stored procedures use a question mark as a variable subsitution character. When using sp_msforeachdb, the "?" returns the databasename, and when using sp_msforeachtable the "?" returns the tablename.

Using sp_msforeachdb

Example #1 - to do a check db on every database in your instance you could issue the following command:

sp_msforeachdb 'dbcc checkdb( ''?'' )'

Example #2 - to change the owner of each database in the instance to sa.

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN
  print ''?''
  exec [?].dbo.sp_changedbowner ''sa''
END'

**Notice how I used an if statement to filter out the system databases

Example #3 - to do a check db on every table in the database you could issue the following command:

sp_msforeachdb 'dbcc checktable( ''?'' )'

Example #4 - to shrink every database on the instance. Be careful with this one. Not something you want to run on a production server during business hours.

sp_msforeachdb 'dbcc ShrinkDatabase( ?, 10 )'

Example #5 - to make a user db_owner on each user database in the instance. This is commonly done for apps like SharePoint that require db_owner in order to apply service packs.

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
   print ''?''

   exec [?].dbo.sp_adduser ''<YOUR DOMAIN NAME HERE>\<YOUR USER ACCOUNT HERE>''
   exec [?].dbo.sp_addrolemember ''db_owner'',''<YOUR DOMAIN NAME HERE>\
        <YOUR USER ACCOUNT HERE''
END'

Using sp_msforeachtable

The counterpart to sp_msforeachdb. Once again, the procedure uses the "?" character to signify the name of the table that the command is currently being executed on.
Example #1 - to get a list of each index and when the statistics were last updated on each index.

CREATE table #stats(
   table_name nvarchar(255) null,
   index_name nvarchar(255) null,
      statistics_update_date datetime null
)
GO
          
exec sp_msforeachtable
'insert into #stats
 SELECT
    ''?'',
        name AS index_name,
    STATS_DATE(object_id, index_id) AS statistics_update_date
 FROM
    sys.indexes
 WHERE
    object_id = OBJECT_ID(''?'');'
      
select * from #stats where index_name is not null
      
drop table #stats

There are a million different uses for these stored procedures. The possibilities are endless. You can even nest a sp_msforeachtable inside of a sp_msforeachdb! Have fun and use them with caution!
 
trovato qui.

Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable

The literal ? is used as a token which is replace with database name or table name according to the executed stored procedure "sp_MSForEachDB" or "sp_MSForEachTable".
If you want to select the database name or the table name as a literal in the t-sql expression you should use double single quotes around the ? literal.
Also the sp_MSForEachDB syntax enables the SQL Server developers or administrators to use [?] instead of ?.
Using token ? in the format "[?]" will rescue in case the database names in the Microsoft SQL Server instance have space characters.
But the same point is just the opposite for the undocumented sp_MSForEachTable proc syntax.

For example, if a database name is "Test Database" then executing the below t-sql command will cause the following error :

EXEC sp_MSForEachDB 'Use ?; SELECT DB_NAME()'
/*
Could not locate entry in sysdatabases for database 'Test'. No entry found with that name. Make sure that the name is entered correctly.
*/


So we can say that the correct syntax for the sp_MSForEachDB and sp_MSForEachTable un-documented procedures is using [?] instead of ? which is pointing to databases in the MSSQL Server

EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'


You can get an idea on how the sp_MSForEachTable syntax works with ? which is representing the table name in the format schema-name.table-name.

EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?' -- SUCCESSFULL EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM [?]' -- FAIL

T-SQL Sample Queries using sp_MSForEachDB

The below t-sql example codes will count database objects and user procedures for each database and will list these count values with the database name beside for the MS SQL Server instance.


EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName, COUNT(*) AS ObjectCount FROM [?].sys.objects'
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName, COUNT(*) AS ObjectCount FROM [?].sys.procedures'


The following sql code sp_MSForEachDB example will list system files for each database in the current MS SQL Server instance.

EXEC sp_MSForEachDB 'SELECT ''?'', * FROM [?]..sysfiles'


And similar to the above sql example listing database files detail, the following t-sql code will run thesp_helpfile for every SQL Server database in the installed SQL Server instance.

EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile'


You can use the "USE" command in order to change the database scope of the query.
This will execute the following query on the related database which is changing everytime with the sp_MSForEachDB.

EXEC sp_MSForEachDB 'USE [?]; SELECT ''?'' AS DatabaseName, COUNT(*) AS ProcedureCount FROM sys.procedures'


Of course, you can remove the EXEC command and make call to the sp_MSForEachDB or sp_MSForEachTable MS SQL Server stored procedures directly.

SHRINKDATABASE For Every Database in the SQL Server Instance using sp_MSForEachDB

The following t-sql sp_MSForEachDB command will shrink every database in the related SQL Server instance.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'


T-SQL Sample Queries using sp_MSForEachTable

The following t-sql query is a statement which displays rows count for each table in a database.

EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'


You should realize that the above select will return the table names in the format [schema name].[table name]
To remove the brackets [ and ] , you can execute the following altered t-sql query.

EXEC sp_MSForEachTable 'SELECT SUBSTRING(''?'', 8, Len(''?'')-8), COUNT(*) FROM ?'


The below t-sql sp_MSForEachTable example will execute the sp_SpaceUsed for everytable in the current MS SQL Server database and will store the results or the outcome of the sp_SpaceUsedsystem stored procedure in the spSpaceUsed table.

CREATE TABLE spSpaceUsed (
  TableName sysname,
  Rows int,
  Reserved varchar(255),
  Data varchar(255),
  Index_Size varchar(255),
  Unused varchar(255)
)
INSERT INTO spSpaceUsed
EXEC sp_MSForEachTable 'EXEC sp_SpaceUsed ''?'''
SELECT * FROM spSpaceUsed


This t-sql query will diplay column names with type and size for every table in a database.

EXEC sp_MSForEachTable '
SELECT
  SUBSTRING(''?'', 8, Len(''?'')-8) AS TableName,
  syscolumns.name ColumnName,
  CASE systypes.name
    WHEN ''sysname'' THEN ''nvarchar''
    ELSE systypes.name
  END AS Type,
  syscolumns.length,
  syscolumns.prec
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
WHERE
  syscolumns.id = (
    SELECT id FROM sysobjects (NoLock)
    WHERE name = SUBSTRING(''?'', 8, Len(''?'')-8)
  )
'


Actually the above t-sql query command will execute just as shown below for let's say the table name is [dbo].[Logs].

SELECT
  SUBSTRING('[dbo].[Logs]', 8, Len('[dbo].[Logs]')-8) AS TableName,
  syscolumns.name ColumnName,
  CASE systypes.name
    WHEN 'sysname' THEN 'nvarchar'
    ELSE systypes.name
  END AS Type,
  syscolumns.length,
  syscolumns.prec
FROM syscolumns (NoLock)
INNER JOIN systypes (NoLock) ON systypes.xtype = syscolumns.xtype
WHERE
  syscolumns.id = (
    SELECT id FROM sysobjects (NoLock)
    WHERE name = SUBSTRING('[dbo].[Logs]', 8, Len('[dbo].[Logs]')-8)
  )


UPDATE STATISTICS For Every Table in the Database using sp_MSForEachTable

The following sql command will update statistics for each table in a database.

EXEC sp_MSForEachTable 'UPDATE STATISTICS ?'


More Tutorials on T-SQL sp_MSForEachTable Examples

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications

 

 

trovato qui.