https://wikitech.raphaelbrenet.com/ 2026-06-13T10:07:50+00:00 https://wikitech.raphaelbrenet.com/ https://wikitech.raphaelbrenet.com/lib/exe/fetch.php?media=wiki:dokuwiki.svg text/html 2022-05-02T14:09:09+00:00 Anonymous (anonymous@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_data_dictionary&rev=1651500549&do=diff SQL Server queries for data dictionary Tables List tables with schema, modification date, number of rows and columns, comments: object_id schema_name table_name create_date modify_date nb_rows nb_columns comments text/html 2020-10-16T09:16:24+00:00 Anonymous (anonymous@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_parse_json&rev=1602839784&do=diff SQL Server parse JSON Read a file and get its content in @jsonValues DECLARE @jsonValues nvarchar(max); SELECT @jsonValues = BulkColumn FROM OPENROWSET (BULK 'D:\path\to\file\datajson.txt',SINGLE_CLOB) as j; Get the first level as key/value table text/html 2022-02-12T11:31:24+00:00 Anonymous (anonymous@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_queries_to_generate_queries&rev=1644665484&do=diff SQL Server queries to generate queries Count rows in all tables Check all tables from a DB schema, with table name starting with D_: DECLARE @schema_name varchar(50); SET @schema_name = 'dwh'; SELECT queryCount = STUFF(( SELECT 'UNION ALL SELECT ''' + @schema_name + '.' + name + ''' as TblName, (SELECT count(*) FROM ' + @schema_name + '.' + name + ') as NbRows ' FROM sys.tables WHERE schema_id = SCHEMA_ID(@schema_name) AND name like 'D[_]%' FOR XML PATH ('') ), 1, 10, '') text/html 2021-02-04T17:17:11+00:00 Anonymous (anonymous@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_recover_dropped_objects_tables_views&rev=1612459031&do=diff SQL Server Recover dropped objects The query below will display the CREATE scripts for dropped object on the specified database and time period DECLARE @Database_Name NVARCHAR(MAX)= 'My_DB_Name' ,@Date_From DATETIME='2021-02-04' ,@Date_To DATETIME ='2021-02-05' ,@Compatibility_Level INT SELECT @Compatibility_Level = dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name IF ISNULL(@Compatibility_Level,0)<=80 BEGIN RAISERROR('The compatibility level sh… text/html 2020-10-16T09:54:16+00:00 Anonymous (anonymous@undisclosed.example.com) https://wikitech.raphaelbrenet.com/doku.php?id=sql:sql_server_see_sessions_and_running_queries&rev=1602842056&do=diff SQL Server Sessions and running queries SELECT SessionId = ses.session_id ,[Database] = DB_Name(er.database_id) ,[Login] = ses.login_name ,Host = ses.host_name ,StartTime = er.start_time ,ClientAddress = con.client_net_address ,SQLStatement = st.text FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections…