TRICKS AND TIPS SQL SERVER--Conocer los detalles de un S
SELECT *
FROM
sys.objects
WHERE type
= 'P'
AND name =
'SP_NAME'
USE
AdventureWorks2012;
GO
EXEC
sp_helptext 'HumanResources.dEmployee';
GO
***********************************
--Cambiar el tipo de dato de un campo en una tabla
ALTER TABLE NombreTabla
ALTER COLUMN CampoTabla int
***********************************
--Agregar un campo a una tabla
ALTER TABLE TABLA ADD CAMPO1 VARCHAR(20) NULL, CAMPO2 INT
NULL ;
********************************
--Eliminar Columnas a tabla
ALTER TABLE TABLA DROP COLUMN columna_eliminar ;
********************************
--ELIMINAR
LLAVE FORANEA
ALTER TABLE
TABLA
DROP
CONSTRAINT FK_Column_B;
GO
******************************
--Crear llave foranea
ALTER TABLE Tabla_inicial
ADD CONSTRAINT FK_Nombre_Llave FOREIGN KEY
(Campo_Tabla_inicial)
REFERENCES
Tabla_final
(Campo_Tabla Final)
ON DELETE CASCADE
ON UPDATE CASCADE;
************************************
--Consultar detalles de un SP
--Buscar un Objeto en la base de datos
SELECT *
FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nombre_obj]')
***********************************
--TrustWorthy--
ALTER
DATABASE NOMBREDB SET TRUSTWORTHY ON;
**********************************
--Respaldar una base de datos
use AdventureWorks2012
go
BACKUP
DATABASE AdventureWorks2012
TO DISK =
'Z:\SQLServerBackups\AdvWorksData.bak'
WITH FORMAT;
GO
*******************************
--CONOCER EL NUMERO DE FILAS DE CADA TABLA DE UNA BASE DE
DATOS
use
[AdventureWorks2014]
go
select
si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as
'Filegroup'
from
sysobjects as SO
join sysindexes as SI on SO.Id = SI.id
join sysfilegroups as SFG on SI.GroupId =
SFG.GroupId
order by
si.rows desc, SO.Name , SI.name, SFG.GroupName
***************************
--cARGAR
CSV A TABLAS
use
[AdventureWorks2014]
go
BULK
INSERT
[HumanResources].[JobCandidate]
FROM
'C:\Users\jonathanm\Desktop\ADwOR\JobCandidate.csv'
WITH (
FIELDTERMINATOR= ',',
ROWTERMINATOR = '\n'
);
go
select *
from [HumanResources].[JobCandidate]
**********************************
-- Encontrar un campo en una base de datos
SELECT
TABLE_NAME,*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE '%meta%'
*****************************************************
--Renombrar una base de datos
USE master;
GO
ALTER
DATABASE AdventureWorks2014
Modify Name
= AdventureWorks ;
GO
****************************************************
----FORZAR
INSERCIÓN DE IDENTITY
SET
IDENTITY_INSERT -TABLA- ON
insert into
-TABLA- values (20, value', getdate(),’value' ,NULL, 1)
SET
IDENTITY_INSERT -TABLA- OFF
*****************************************************
--PONER EN OFF LINE Y ONLINE UNA BASE DE DATOS
ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
USE master
GO
ALTER DATABASE YourDatabaseName SET ONLINE
GO
*******************************************************
--Comprabar existencia de una tabla
IF NOT
EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('[dbo].[tabla]'))
*******************************************************
--Comprobar si existe un campo en una tabla
IF NOT
EXISTS (SELECT * FROM SYS.columns WHERE
name='campo' AND object_id = OBJECT_ID(N'[dbo].[tabla]'))
*******************************************************
--Comprobar
si existe un SP
IF EXISTS (SELECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'[dbo].[Nombre_SP]'))
*******************************************************
--Comprobar permisos de un usuario
EXEC sp_helprotect NULL,'Usuario';
********************************************************
--reducir log de una BD
USE AdventureWorks2012;
GO
-- Truncate
the log by changing the database recovery model to SIMPLE.
ALTER
DATABASE AdventureWorks2012
SET
RECOVERY SIMPLE;
GO
-- Shrink
the truncated log file to 1 MB.
DBCC
SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset
the database recovery model.
ALTER
DATABASE AdventureWorks2012
SET
RECOVERY FULL;
GO
************************************************************
--BUSCAR UN DATO EN TODA LA BASE DE DATOS
CREATE
PROCEDURE BuscaValorEnBBDD
(
@StrValorBusqueda
nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Resultado (NombreColumna nvarchar(370),
ValorColumna nvarchar(3630))
SET NOCOUNT
ON DECLARE @NombreTabla nvarchar(256),
@NombreColumna
nvarchar(128),
@StrValorBusqueda2
nvarchar(110)
SET
@NombreTabla = ''
SET
@StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''')
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')
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 ' +
@StrValorBusqueda2)
END
END
END
SELECT
NombreColumna, ValorColumna FROM #Resultado
END
Exec BuscaValorEnBBDD ‘PALABRA O VALOR A BUSCAR’
*****************************************************************************************
--BUSCAR TODAS LAS llaves primarias DE TODAS LAS TABLAS DE
UNA BASE DE DATOS
SELECT
i.name AS NombreIndice,
OBJECT_NAME(ic.OBJECT_ID)
AS NombreTabla,
COL_NAME(ic.OBJECT_ID,ic.column_id)
AS NombreColumna
FROM
sys.indexes AS i
INNER JOIN
sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND
i.index_id = ic.index_id and i.is_primary_key = 1
*****************************************************************************************
-- Buscar TODAS LAS columnas identity en base de datos
SELECT
SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,'SchemaId')) AS SchemaName,
OBJECT_NAME(OBJECT_ID) AS TableName,
name AS ColumnName
FROM SYS.COLUMNS
WHERE
is_identity = 1
ORDER BY
SchemaName, TableName, ColumnName
*********************************************************************************
--CONOCER EL NÚMERO TOTAL DE REGISTROS DE TU BASE DE DATOS:
USE [MI_BASE]
GO
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TABLA], SUM(sPTN.Rows) AS [REGISTROS]
INTO #TMPCount
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY registros desc
GO
select sum ([REGISTROS]) [TOTAL_REG]from #TMPCount
drop table #TMPCount
**********************************************************************
---CÓDIGO TRANSACCIONAL CON MANEJO DE ERRORES Y ROLLBACK EN CASO DE ERROR
BEGIN TRANSACTION;
BEGIN TRY
--------------------------------------------------
-------COLOCAR AQUÍ EL SCRIPT---------------------
--------NO USAR "GO"------------------------------
--------------------------------------------------
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
--CONOCER EL NÚMERO TOTAL DE REGISTROS DE TU BASE DE DATOS:
USE [MI_BASE]
GO
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TABLA], SUM(sPTN.Rows) AS [REGISTROS]
INTO #TMPCount
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY registros desc
GO
select sum ([REGISTROS]) [TOTAL_REG]from #TMPCount
drop table #TMPCount
**********************************************************************
---CÓDIGO TRANSACCIONAL CON MANEJO DE ERRORES Y ROLLBACK EN CASO DE ERROR
BEGIN TRANSACTION;
BEGIN TRY
--------------------------------------------------
-------COLOCAR AQUÍ EL SCRIPT---------------------
--------NO USAR "GO"------------------------------
--------------------------------------------------
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
**********************************************************************
No hay comentarios:
Publicar un comentario