Get columns of a table: only columns with values

If you require retrieving a table’s column list, specifically those containing values, you can do so

DECLARE @TableName NVARCHAR(128) = 'BFDOCD'; 

DECLARE @SQLQuery NVARCHAR(MAX) = '';

SELECT @SQLQuery = @SQLQuery +
    CASE 
        WHEN DATA_TYPE IN ('int', 'smallint', 'bigint', 'decimal', 'numeric', 'float', 'real') THEN 
            'SELECT ''' + COLUMN_NAME + ''' AS ColumnName FROM ' + @TableName + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL AND ' + QUOTENAME(COLUMN_NAME) + ' != 0 UNION '
        ELSE
            'SELECT ''' + COLUMN_NAME + ''' AS ColumnName FROM ' + @TableName + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL UNION '
    END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = 'dbo' -- Modificare se lo schema non è dbo

SET @SQLQuery = LEFT(@SQLQuery, LEN(@SQLQuery) - 6); -- Rimuove l'ultimo UNION

EXEC sp_executesql @SQLQuery;

Find all tables containing column with specified name

SELECT t.name AS 'TableName', c.name  AS 'ColumnName', c.max_length
FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%UTEN%' and c.max_length=28
ORDER BY TableName, ColumnName;

Still on database space used

Total space occupied by the database

SELECT DB_NAME(database_id) AS DatabaseName, Sum(size) * 8 / 1024 AS TotalSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = DB_NAME()
GROUP BY DB_NAME(database_id);

Space occupied by user tables

SELECT t.NAME AS TableName, Sum(p.rows) AS RowCounts, Sum(a.total_pages) * 8 / 1024 AS TotalSpaceMB
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
WHERE t.is_ms_shipped = 0
GROUP BY t.NAME
ORDER BY TotalSpaceMB DESC;

Space occupied by FILESTREAM

SELECT t.NAME AS TableName, Sum(au.total_pages) * 8 / 1024 AS FileStreamSpaceMB
FROM sys.allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.partition_id
	INNER JOIN sys.tables t ON p.object_id = t.object_id
WHERE t.filestream_data_space_id IS NOT NULL
GROUP BY t.NAME;

Space occupied externally from the database by tables with FILESTREAM fields

SELECT t.name AS TableName, Sum(df.size * 8 / 1024.0) AS FileStreamExternalSpaceMB
FROM sys.tables t JOIN sys.filegroups fg ON t.filestream_data_space_id = fg.data_space_id
	JOIN sys.database_files df ON fg.data_space_id = df.data_space_id
GROUP BY t.name;

Table with varbinary column in filestream group

1. Make sure the FileStream filegroup has already been created in your database
2. Creates the table without specifying the filegroup for the VARBINARY column

CREATE TABLE [dbo].[RECOMA](
    [RECOMACOMU] [int] NOT NULL,
    [RECOMAPROG] [int] NOT NULL,
    [RECOMANOME] [nvarchar](100) NULL,
    [RECOMAGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
CONSTRAINT [PK_RECOMA] PRIMARY KEY CLUSTERED (
    [RECOMACOMU] ASC,
    [RECOMAPROG] ASC
)
)

3. Add the VARBINARY column to the FileStream filegroup using the ALTER TABLE statement

ALTER TABLE [dbo].[RECOMA]
ADD [RECOMAFILE] VARBINARY(MAX) FILESTREAM NULL;

You can also specify the name of the filegroup to which to assign the varbinary column

ALTER TABLE [dbo].[RECOMA]
ADD [RECOMAFILE] VARBINARY(MAX) FILESTREAM FILEGROUP FileStreamGroup1;

Filestream: force garbage collection

The FILESTREAM Garbage Collector runs automatically. If you need to remove a container or clean a container before the Garbage Collector has run, you can use sp_filestream_force_garbage_collection to run the Garbage Collector manually

EXEC sp_filestream_force_garbage_collection 'database-name'

Filetable: directory permissions

With a FileTable, SQL Server performs authorization checks based on the user’s Windows account. The Windows account must have permissions to connect to the SQL instance and database along with permission on the FileTable, just like when the table is used directly in T-SQL. These permissions can be assigned directly or via Windows group membership

CREATE LOGIN [YourDomain\WindowsUserOrGroup] FROM WINDOWS;
CREATE USER [YourDomain\WindowsUserOrGroup];
GRANT SELECT ON dbo.YourFileTable TO [YourDomain\WindowsUserOrGroup];

Filestream error

In order to avoid the AlwaysOn groups availability error in SQLserver 2019

DBCC TRACEON(12324, -1) 

Database size

In order to know the size of a database (rows) and its log file

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

Table Type

CREATE TYPE [dbo].[AAMPRD_tipoTabella] AS TABLE(
	[AAMPRDCLIE] [nvarchar](50) NOT NULL,
	[AAMPRDDITT] [nvarchar](50) NOT NULL,
	[AAMPRDPROF] [nvarchar](50) NOT NULL,
	[AAMPRDVOCE] [nvarchar](50) NOT NULL,
	[AAMPRDVISI] [nvarchar](1) NULL
)
GO

This sql script create an user defined Table Type in SQLserver. You can use an user table type in a stored procedure as a parameter

ALTER PROCEDURE [dbo].[AAMPRD_aggiornaVoci] 
	@tabella1 AAMPRD_tipoTabella READONLY
AS

	DELETE AAMPRD 
	FROM AAMPRD INNER JOIN @tabella1 T1 ON AAMPRD.AAMPRDCLIE=T1.AAMPRDCLIE 
		AND AAMPRD.AAMPRDDITT=T1.AAMPRDDITT 
		AND AAMPRD.AAMPRDPROF=T1.AAMPRDPROF 
		AND AAMPRD.AAMPRDVOCE=T1.AAMPRDVOCE

	INSERT INTO AAMPRD(AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI)
	SELECT AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI
	FROM @tabella1

You can declare a variable with table type created: the attribute READONLY is mandatory.

In .Net you can pass the correct value to the table type parameter using a SQLparameter ,i.e. assigning directly a datatable to the value of SQLparameter

You can also test your table in this way

DECLARE @t1 as AAMPRD_tipoTabella

INSERT @t1(AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI)
VALUES ('aaa1', 'aaa1', 'prof1', 'v1', 'S'), 
      ('bbb1', 'bbb1', 'prof1', 'v1', 'S'), 
      ('ccc1', 'ccc1', 'prof1', 'v1', 'S')

SELECT * FROM @t1

List of locked tables

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description, oggetti.object_id, oggetti.name As nomeTabella, oggetti.type_desc 
FROM sys.dm_tran_locks l, sys.objects oggetti
WHERE l.resource_associated_entity_id = oggetti.object_id
    and resource_database_id = DB_ID()

Script sql to get the list of the tables locked in the current database