USE MASTER;
GO
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
SELECT name, physical_name,state,size
FROM ?.sys.database_files
END';GO
ReferenciasGO
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
SELECT name, physical_name,state,size
FROM ?.sys.database_files
END';GO
Referencias/********** ejemplo 1 **********/ DECLARE @au_id CHAR( 11 )
SET ROWCOUNT 0 SET ROWCOUNT 0
SELECT * FROM #mytemp WHERE au_id = @au_id
DELETE #mytemp WHERE au_id = @au_id
SET ROWCOUNT 1
SELECT @au_id = au_id FROM #mytempEND
SET ROWCOUNT 0
GO
/********** ejemplo 2 **********/
DECLARE @au_id CHAR( 11 )
SELECT @au_id = MIN( au_id ) FROM authors
WHILE @au_id IS NOT NULLBEGIN
SELECT * FROM authors WHERE au_id = @au_id
SELECT @au_id = MIN( au_id ) FROM authors WHERE au_id > @au_idENDGO
/********** ejemplo 3 **********/
SET ROWCOUNT 0SELECT NULL mykey, * INTO #mytemp FROM authors
SET ROWCOUNT 1UPDATE #mytemp SET mykey = 1
WHILE @@rowcount > 0BEGIN
SET ROWCOUNT 0
SELECT * FROM #mytemp WHERE mykey = 1
DELETE #mytemp WHERE mykey = 1
SET ROWCOUNT 1
UPDATE #mytemp SET mykey = 1END
SET ROWCOUNT 0
GO
SELECT TOP 25
o.name AS ObjectName, i.name AS IndexName, i.index_id AS IndexID, dm_ius.user_seeks AS UserSeek, dm_ius.user_scans AS UserScans, dm_ius.user_lookups AS UserLookups, dm_ius.user_updates AS UserUpdates, p.TableRows, 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_IDWHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASCGO
Referencias:SELECTa.name AS LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,CASE
WHEN b.sysadmin = 1 THEN 'sysadmin'
WHEN b.securityadmin=1 THEN 'securityadmin'
WHEN b.serveradmin=1 THEN 'serveradmin'
WHEN b.setupadmin=1 THEN 'setupadmin'
WHEN b.processadmin=1 THEN 'processadmin'
WHEN b.diskadmin=1 THEN 'diskadmin'
WHEN b.dbcreator=1 THEN 'dbcreator'
WHEN b.bulkadmin=1 THEN 'bulkadmin'ELSE 'Public' END AS 'ServerRole'FROM sys.server_principals a JOIN MASTER..syslogins b
ON a.sid=b.sid WHERE a.TYPE <> 'R'
AND a.name NOT LIKE '##%'
DECLARE @DBuser_sql VARCHAR(4000);DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200));
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name';
INSERT @DBuser_tableEXEC sp_MSforeachdb @command1 = @dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName;GO
DECLARE @Obj_sql VARCHAR(2000);DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200));
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object, permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name ';
INSERT @Obj_table
EXEC sp_msforeachdb @command1 = @Obj_sql
SELECT * FROM @Obj_table;GO
Referencias:
EXEC MASTER.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
@ruta OUTPUT,
'no_output';
SELECT @ruta;
DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [float] NULL
,[c_val] [varchar](128) NULL
)
INSERT INTO @xp_msverEXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]AS (
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [number_of_cores_per_cpu]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [total_number_of_cores]
,[cpu_count] AS [number_of_virtual_cpus]
,(
SELECT [c_val]
FROM @xp_msver
WHERE [c_name] = 'Platform'
) AS [cpu_category]
FROM [sys].[dm_os_sys_info]
)SELECT [number_of_physical_cpus]
,[number_of_cores_per_cpu]
,[total_number_of_cores]
,[number_of_virtual_cpus]
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]FROM [ProcessorInfo]
Referencias:
Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) script
DECLARE @sql_server_service_account SYSNAME;DECLARE @sql_server_agent_service_account SYSNAME;
EXECUTE MASTER.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @sql_server_service_account OUTPUT
EXECUTE MASTER.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @sql_server_agent_service_account OUTPUT
SELECT [SQL Server Service Account] = @sql_server_service_account,
[SQL Server Agent Service Account] = @sql_server_agent_service_account;GO
SELECT servicename, service_accountFROM sys.dm_server_services
GO
DECLARE @endDate DATETIME, @months smallint;SET @endDate = GETDATE(); -- Incluir las estadísticas de las copias de respaldo de hoy.SET @months = 6; -- hasta 6 meses atrás.
;WITH HIST AS
(SELECT BS.database_name AS DatabaseName
,YEAR(BS.backup_start_date) * 100
+ MONTH(BS.backup_start_date) AS YearMonth
,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
FROM msdb.dbo.backupset AS BS
INNER JOIN
msdb.dbo.backupfile AS BF
ON BS.backup_set_id = BF.backup_set_id
WHERE NOT BS.database_name IN
('master', 'msdb', 'model', 'tempdb')
AND BF.file_type = 'D'
AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
GROUP BY BS.database_name
,YEAR(BS.backup_start_date)
,MONTH(BS.backup_start_date))SELECT MAIN.DatabaseName
,MAIN.YearMonth
,MAIN.MinSizeMB
,MAIN.MaxSizeMB
,MAIN.AvgSizeMB
,MAIN.AvgSizeMB
- (SELECT TOP 1 SUB.AvgSizeMB
FROM HIST AS SUB
WHERE SUB.DatabaseName = MAIN.DatabaseName
AND SUB.YearMonth < MAIN.YearMonth
ORDER BY SUB.YearMonth DESC) AS GrowthMBFROM HIST AS MAINORDER BY MAIN.DatabaseName
,MAIN.YearMonth;
GO
-- Cambiar al contexto de la base de datos master
USE MASTER;
GO
-- Llevar la base de datos a single user mode
-- Esto termina todas las conexiones a la base de datos
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Desacoplar la base de datos
EXEC MASTER.dbo.sp_detach_db @dbname = N'dbTest'
GO
--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--
-- Re-acoplar la base de datos
CREATE DATABASE [TestDB] ON
(FILENAME = N'E:\RutaNueva\dbTest.mdf' ),
(FILENAME = N'E:\RutaNueva\dbTest_log.ldf' )
FOR ATTACH
GO
-- Cambiar al contexto de la base de datos master
USE MASTER;
GO
-- Cambiar el estado de la base de datos a modo de usuario único
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Cambiar el estado de la base de datos a fuera de línea
ALTER DATABASE dbtest SET OFFLINE;
GO
--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--
-- Re-apunta la ruta del archivo mdf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY FILE
(NAME='dbTest', FILENAME='E:\NuevaRuta\dbTest.mdf');
-- Re-apunta la ruta del archivo ldf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY FILE
(NAME='dbTest_Log', FILENAME='E:\NuevaRuta\dbTest_log.ldf');
GO
-- Cambiar el estado de la base de datos a en-línea
ALTER DATABASE TEMP SET ONLINE;
GO
-- Cambiar el estado de la base de datos a modo multi-usuario
ALTER DATABASE TEMP SET MULTI_USER;
GO
-- Cambiar al contexto de la base de datos master
USE MASTER;
GO
-- Desacoplar la base de datos
EXEC sp_detach_db @dbname = N'dbTest';
--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--
-- Reacoplar la base de datos con los archivos en la nueva instancia y nueva ubicación
CREATE DATABASE dbTest
ON(NAME='dbTest',
FILENAME='e:\NuevaRuta\dbTest.mdf')
LOG ON(NAME='MyDatabase_Log',
FILENAME='e:\NuevaRuta\dbTest_log.ldf')
FOR ATTACH
WITH ENABLE_BROKER;
GO
En la actualidad utilizamos y desarrollamos un sinnúmero de aplicaciones basadas en tecnologías móviles y el motor de base de datos por excelencia para este tipo de aplicaciones es SQLite, líder indiscutible en este segmento de tecnológico. En ocasiones nos veremos obligados a importar datos desde SQLite hacia SQL Server, y este artículo tiene como finalidad mostrar una guía paso a paso de cómo realizar esta tarea que ha cobrado tanta importancia en estos momentos donde el auge de las aplicaciones para dispositivos móviles crece a un ritmo muy acelerado.
SoluciónExisten varias formas alternativas de como importar los datos desde SQLite hacia SQL Server, nosotros como solución implementaremos un Linked Server hacia SQLite.
Dividiremos esta solución en los siguientes pasos:
Elija el controlador que le corresponda dependiendo de si su sistema operativo es de 32 o 64 bits y ejecute el archivo ejecutable (.exe) correspondiente.
USE [master]
GO
EXEC sp_addlinkedserver
@server = 'Mobile_Phone_DB_64', -- Nombre con el que identificaras el Link Server en SSMS
@srvproduct = '', -- Puede estar en blanco pero no puede ser NULL
@provider = 'MSDASQL',
@datasrc = 'Mobile_Phone_DB_64' -- El nombre de la conexión DSN de Sistema
GO
SELECT *
FROM OPENQUERY(Mobile_Phone_DB_64 , 'select * from db_notes')
GO
SELECT * INTO SQLite_Data -- Esto crea una tabla nueva con los datos seleccionados
FROM OPENQUERY(Mobile_Phone_DB_64 , 'select * from db_notes')
GO
USE MASTER
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue VARBINARY(256),
@hexvalue VARCHAR (514) OUTPUT
AS
DECLARE @charvalue VARCHAR (514)
DECLARE @i INT
DECLARE @length INT
DECLARE @hexstring CHAR(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint INT
DECLARE @firstint INT
DECLARE @secondint INT
SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name SYSNAME = NULL AS
DECLARE @name SYSNAME
DECLARE @type VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary VARBINARY (256)
DECLARE @PWD_string VARCHAR (514)
DECLARE @SID_varbinary VARBINARY (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)
DECLARE @defaultdb SYSNAME
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS VARBINARY (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
EXEC sp_help_revlogin;
GO