select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30) from dbo.sysfiles a
select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30) from dbo.sysfiles a
https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server Szukanie ciągu znaków we wszystkich tabelach (dla sqlsrv < 2016) DECLARE @search_string VARCHAR(100), @table_name SYSNAME, @table_id INT, @column_name SYSNAME, @sql_string VARCHAR(2000) SET @search_string = 'STRING' DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE type = 'U' OPEN tables_cur FETCH NEXT FROM tables_cur INTO @table_name, @table_id WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239) OPEN columns_cur FETCH NEXT FROM columns_cur INTO @column_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + '''' EXECUTE(@sql_string) FETCH NEXT FROM columns_cur INTO @column_name END CLOSE columns_cur DEALLOCATE columns_cur FETCH NEXT FROM tables_cur INTO @table_name, @table_id END CLOSE tables_cur DEALLOCATE tables_cur Szukanie ciągu znaków we wszystkich tabelach (dla sqlsrv >= 2016) nie testowałem ...
SQL Server Config manager error: Cannot connect to WMI provider Run Cmd as Administrator and execute these commands: First go to SQL Shared folder according to your sql version: SQL 2008: C:\Program Files (x86)\Microsoft SQL Server\100\Shared\ SQL 2012: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\ SQL 2014: C:\Program Files (x86)\Microsoft SQL Server\120\Shared\ SQL 2017: C:\Program Files (x86)\Microsoft SQL Server\140\Shared\ -------> My version is 2017 SQL 2019: C:\Program Files (x86)\Microsoft SQL Server\150\Shared\ Find more versions here cd "C:\Program Files (x86)\Microsoft SQL Server\140\Shared" Then: ...
--CREATE 5 RANDOM ORDERS BY CUSTOMER ID 4, MONITOR FOR ALERTS IN SLACK. WHILE (@I < 5) BEGIN SET @M = (SELECT ROUND(RAND() * 12, 0)) SET @D = (SELECT ROUND(RAND() * 28, 0)) SET @Y = (SELECT 2020) SET @DT = (SELECT CAST(@M AS VARCHAR(2)) + '/' + CAST(@D AS VARCHAR(2)) + '/' + CAST(@Y AS VARCHAR(4))) SET @AMT = (SELECT 1 + ROUND(RAND() * (600 + 1 - 1), 0)) INSERT INTO Orders VALUES(@DT,@AMT,'Random',4) SET @I = @I + 1 WAITFOR DELAY '00:00:05' END
<?php $serverName = "127.0.0.1,1433"; //serverNameinstanceName, portNumber (default is 1433) $connectionInfo = array( "Database"=>"demo", "UID"=>"sa", "PWD"=>"pass"); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn ) { echo "Connection established.<br />"; }else{ echo "Connection could not be established.<br />"; die( print_r( sqlsrv_errors(), true)); } ?>
MSSQL - rozmiar tabel SELECT DF.name as [Plik] --,[File_Location] = DF.PHYSICAL_NAME ,[Rozmiar (MB)] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0) ,[Uzyte (MB)] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)) ,[Wolne (MB)] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0) ,[Wolne (%)] = CONVERT(DECIMAL(10,2),((DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)/(DF.SIZE/128.0))*100) FROM sys.database_files DF LEFT JOIN sys.filegroups FG ON DF.data_space_id = FG.data_space_id SELECT -- s.Name AS SchemaName, t.Name AS [Tabela], p.rows AS [Ilość rekordów], CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS [Rozmiar (MB)], CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS [Uzyte (MB)], CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS [Wolne (MB)] FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY [Rozmiar (MB)] DESC GO
Preferences: Open Settings (JSON) a w nim konfiguracja połączeń MSSQL: "mssql.connections": [ { "server": "127.0.0.1,52019", "database": "", "authenticationType": "SqlLogin", "user": "sa", "password": "", "emptyPasswordInput": false, "savePassword": true, "profileName": "SQL2019" } ],
Jeśli nie znam hasła użytkownika sa, ale mam dostęp do konta windows z uprawnieniami do serwera SQL (najczęściej użytkownik który instalował instancję SQL) możemy zmienić hasło użytkownika sa. W tym celu łączę się z serwerem SQL: osql -S SERWERSQL –E po połączeniu zmieniamy hasło: sp_password 'Wapro3000',’noweHasl0’ go -- lub tak: ALTER LOGIN sa WITH PASSWORD = 'noweHasl0' go
Odbudowa indeksów dla wszystkich tabel w bazie danych: GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO