SQL Server - Search in multiple tables - Buscar texto en varias tablas

CREATE PROC [dbo]. [spBuscarTexto]
(
@TextoBuscar nvarchar (100)
)
AS

BEGIN
CREATE TABLE #Resultado (NombreColumna nvarchar(370 ), ValorColumna nvarchar( 3630))

SET NOCOUNT ON

DECLARE @NombreTabla nvarchar( 256), @NombreColumna nvarchar (128), @TextoBuscar2 nvarchar(110 )
SET  @NombreTabla = ''
SET @TextoBuscar2 = QUOTENAME('%' + @TextoBuscar + '%', '''')

WHILE @NombreTabla IS NOT NULL

BEGIN
SET @NombreColumna = ''
SET @NombreTabla =
(
SELECT MIN (QUOTENAME( TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME ))
FROM INFORMATION_SCHEMA .TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME (TABLE_SCHEMA) + '.' + QUOTENAME( TABLE_NAME) > @NombreTabla
AND OBJECTPROPERTY (
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA ) + '.' + QUOTENAME (TABLE_NAME)
), 'IsMSShipped'
  ) = 0
)
WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL)

BEGIN
SET @NombreColumna =
(
SELECT MIN (QUOTENAME( COLUMN_NAME))
FROM INFORMATION_SCHEMA .COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla , 2)
AND TABLE_NAME = PARSENAME(@NombreTabla , 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'uniqueidentifier')
AND QUOTENAME (COLUMN_NAME) > @NombreColumna
)

IF @NombreColumna IS NOT NULL

BEGIN
INSERT INTO #Resultado
EXEC
(
'SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630)
FROM ' + @NombreTabla + ' (NOLOCK) ' +
' WHERE ' + @NombreColumna + ' LIKE ' + @TextoBuscar2
)
END
END
END

SELECT NombreColumna , ValorColumna FROM #Resultado ORDER BY NombreColumna
END




[spBuscarTexto] 'texto buscado'

StreamReader Encoding: accents problem - problema con acentos

Use
System.Text.Encoding.Default

 StreamReader objReader = new StreamReader( file, System.Text.Encoding.Default, false);

Cómo buscar objetos de base de datos consultando tablas de sistema (SQL SERVER)

/* ================================
Get the list of all databases
================================ */

select * from sys.databases — SQL Server 2005
Select * from sysdatabases — SQL Server 2000

–Tip:When you are in SQL Server Management Studio or Query Analyzer, Press Ctrl + U, it will activate drop down will all user defined databases.

/* ================================
Get Current Database Name
================================ */
Select Db_Name()

/* ================================
Get list of database files for a specific database
================================ */

Select * from sys.sysfiles — sql server 2005
or
select * from sysfiles — sql server 2000

/* ================================
Get the list of all logins in a server
================================ */

Select * from syslogins — sql server 2005
or
select * from sysxlogins — sql server 2000

/* ================================
Get Current logged in login Name
================================ */

Select suser_sname()

/* ================================
Get the list of all users in a database
================================ */
Select * from Sysusers — sql server 2000
Select * from sys.Sysusers — SQL Server 2005

/* ================================
Get Current logged in User Name
================================*/
Select user_name()

/* ====================================
Get a list of all objects in a database
=======================================*/

Select * from sysobjects — sql server 2000
or
Select * from sys.all_objects — SQL Server 2005

/* ================================
Get list of all user defined tables.
=====================================*/

Select * from information_schema.tables
or
Select * from sys.tables — SQL Server 2005

/* ================================
To get list of all views
================================*/
Select * from sys.views order by create_Date desc — SQL Server 2005

/* ================================
To get list of all procedures,
================================*/

Select * from sys.procedures order by create_Date desc — SQL Server 2005
/*================================
To get list of columns
================================*/
Select * from Information_Schema.columns

/*================================================================
Get the definition (Original text) of procedure, view, function.
================================*================================*/
Exec Sp_Helptext Object_Name
or
Exec Sp_Helptext [owner.objectname]

/*================================================================================================
Get Table ,View structure, information about indexes, constraints, data type, data length.
================================================================================================*/
Exec Sp_Help object_name
or
Exec Sp_Help [owner.object_name]

/*================================================================
Get all dependency on a particular object.
================================================================*/
Sp_depends object_name

/*================================================================
Get list of orphaned (sql server) users.
================================================================*/
Sp_change_users_login ‘report’ :

/* ================================================================
Get Object Level Permision for each user or db_role
================================================================*/
sp_helprotect
or
sp_helprotect @user_name = ‘name of the user’

-----------------------------------------------

Pagina fuente: http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-display-code-text-of-stored-procedure-trigger-view-or-object/

SQLSERVER - Update with JOIN

UPDATE t1
SET
t1.Estado = 2
FROM table t1
INNER JOIN table2 t2  ON t1.ID = t2.ID
WHERE
t2.field= @Condicion

SQLSERVER - generate Insert - Procedure to script your data

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.

1- Run script  Script

 2- Examples

Example 1:	To generate INSERT statements for table 'titles': EXEC sp_generate_inserts 'titles'
Example 2:	To ommit the column list in the INSERT statement: (Column list is included by default) NOTE: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results EXEC sp_generate_inserts 'titles', @Include_Column_List = 0
Example 3:	To generate INSERT statements for 'titlesCopy' table from 'titles' table: EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4:	To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them: EXEC sp_generate_inserts 'titles', @From = "from titles where title like '%Computer%'"
Example 5:	To specify that you want to include TIMESTAMP column's data as well in the INSERT statement: NOTE: By default TIMESTAMP column's data is not scripted EXEC sp_generate_inserts 'titles', @Include_Timestamp = 1
Example 6:	To print the debug information: EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7:	If you are not the owner of the table, use @owner parameter to specify the owner name: NOTE: To use this option, you must have SELECT permissions on that table EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8:	To generate INSERT statements for the rest of the columns excluding images: NOTE: When using this otion, DO NOT set @include_column_list parameter to 0 EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9:	To generate INSERT statements for the rest of the columns excluding IDENTITY column: EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10:	To generate INSERT statements for the top 10 rows in the table: EXEC sp_generate_inserts mytable, @top = 10
Example 11:	To generate INSERT statements only with the columns you want: EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12:	To generate INSERT statements by ommitting some columns: EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13:	To avoid checking the foreign key constraints while loading data with INSERT statements: NOTE: The @disable_constraints option will disable foreign key constraints, by assuming that the source data is valid and referentially sound EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14:	To avoid scripting data from computed columns: EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

SQL SERVER - DatePart

select DATEPART ( hour , getdate() )

SQL SERVER - DateDiff

select datediff(day,'2012-01-01',getdate())