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
Zapytania SQL na danych z pliku CSV https://til.simonwillison.net/sqlite/one-line-csv-operations
<?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)); } ?>
PC-Market: zmiana kodów krótkich na wagowe Select * from Towar where kod like '29%' Select getdate(), '29' + right('0000' + kod, 4) + '???????', * from Towar where JMid = 2 AND len(kod) <=4 order by kod UPDATE Towar set kod = '29' + right('0000' + kod, 4) + '???????', zmiana = getdate(), zmianaistotna = getdate() where JMid = 2 AND len(kod) <=4 -- AND asid not in(134,132) lub tak: UPDATE [dbo].[Towar] SET [Kod] = cast(290000 + cast(kod as int) as varchar) + '???????' WHERE Towid < 10 GO select * from towar where towid < 50
Problem Transmisja zatrzymuje się na jednym z dokumentów. Rozwiązanie 11234 - id dokumentu na którym staje łączność Select top 100 externalid, * from receipt where receiptid > 11230 UPDATE receipt set isactive = 0 where receiptid = 11234
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" } ],