Find large tables size in SQL Server

SQL Server > Scripts

Microsoft SQL Server has two undocumented stored procedures that allow you to process through all tables in a database (sp_MSforeachtable), or all databases (sp_MSforeachdb).

sp_MSforeachtable

Example: Find tables size in SQL Server

SET NOCOUNT ON

CREATE TABLE #tbl_size
(
       tbl_name            sysname ,
       rows int,
       reserved_size VARCHAR(50),
       data_size           VARCHAR(50),
       index_size          VARCHAR(50),
       unused_size         VARCHAR(50)
)
INSERT #tbl_size
EXEC sp_msforeachtable 'sp_spaceused ''?'''
select
       *
from
       #tbl_size
order by
       CAST(REPLACE(data_size, ' KB', '') AS int) desc

drop table #tbl_size

Concatenate rows from table into a single text string SQL Server

SQL Server > XML Data > FOR XML > Concatenate rows into text lines

create table #person
(
       id           int,
       name   nvarchar(50)
)
insert into #person(id, name)
values (1,'john')
insert into #person(id, name)
values (1,'smith')
insert into #person(id, name)
values (2,'laura')
insert into #person(id, name)
values (2,'stan')

select * from #person

1      john

1      smith
2      laura
2      stan

select distinct

       p2.id,
    substring((Select ','+ p1.name   AS [text()] From #person p1 Where p1.id = p2.id
                ORDER BY p1.id For XML PATH ('')),2, 1000) name
         From #person p2

1 john,smith

2 laura,stan
drop table #person

No cache ASP.NET

ASP.NET > HttpCachePolicy > SetCacheability

HttpCachePolicy.SetCacheability Method (HttpCacheability)

Sets the Cache-Control header to one of the values of HttpCacheability.


Example:

Set the Cache-Control header to no-cache.

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Last date of the month SQL Server

SQL Server > Built-In Functions > EOMONTH

EOMONTH returns the last day of the month that contains the specified date

 

SELECT EOMONTH(GETDATE())

For older editions ( < SQL Server 2012 )

SELECT CAST(DATEADD(d,-1,(DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0)))AS DATE)

ComVisibleAttribute C#

C# > InteropServices > ComVisible

ComVisible Attribute indicates that the managed type is visible to COM.
The default value is true.

Examples:


1. AssemblyInfo.cs
// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
     [assembly: ComVisible(false)]

2.ComVisible in classes and methods
   [ComVisible(false)] //the class and its members are invisible to COM
   class ClassA
   {
        public ClassA()
        {
        }
        //if you derive ClassB from ClassA and export ClassB to COM, ClassA Method1 become visible to COM
        public int Method1()
        {
            return 0;
        }
        [ComVisible(false)] // Method2 is invisible to COM
        public int Method2()
        {
            return 1; 
        }
   }

 

DllImportAttribute C#

C# > InteropServices > DllImportAttribute

Indicates that the attributed method is exposed by an unmanaged dynamic-link library (DLL) as a static entry point.

Example:

using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace WindowsFormsApplication3

{
    public partial class Form1 : Form
    {
        // Import Win32 MessageBox function.
        [DllImport("user32.dll", CharSet = CharSet.Unicode)]
        public static extern int MessageBox(IntPtr hWnd, String text, String caption, uint type);
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // Call the MessageBox function with platform invoke.
            MessageBox(new IntPtr(0), "Text!", "Title", 0);
        }
    }
}

System.Runtime.InteropServices Namespace

System.Runtime.InteropServices Namespace

Provides a list of members that support COM interop and platform invoke services.