How to find table row count?

--Use below query to find table row count
select so.name,sp.rows from sys.objects so inner join sys.partitions sp
on so.object_id = sp.object_id
where so.type in ('TF','U','V')
and sp.index_id <= 1
order by so.name

2 SQL Server query to find all permissions/access for all users in a database

SELECT
        [UserType] = CASE princ.[type]
        WHEN 'S' THEN 'SQL User'
        WHEN 'U' THEN 'Windows User'
        WHEN 'G' THEN 'Windows Group'
        END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
        WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
        ELSE perm.[class_desc]             -- Higher-level objects
         END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
        WHEN 3 THEN permschem.[name]       -- Schemas
        WHEN 4 THEN imp.[name]             -- Impersonations
         ELSE OBJECT_NAME(perm.[major_id])  -- General objects
         END,
        [ColumnName] = col.[name]
    FROM
        --Database user 
sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

Script to check Link server name in View and Store Procedure

--Script to check Link server name in View / SP SELECT

Distinct

referenced_Server_name As LinkedServerName,

referenced_schema_name AS LinkedServerSchema,

referenced_database_name AS LinkedServerDB,

referenced_entity_name As LinkedServerTable,

OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer

FROM sys.sql_expression_dependencies

WHERE referenced_database_name IS NOT NULL

And referenced_Server_name in ('YourLinkServerName') ---Script to check Link server in SQL Server 2005 SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%YourLinkServerName%'

How to download SQL Server 2016 / 2017 Free

How to download SQL Server 2016 / 2017 Free:

It's simple please go through below link to download free, nowadays Microsoft giving Developer edition free download, because they love developers and you get all feature whatever you get in the enterprise version, soo cool right, but only one catch you can't use developer for your production environment.

Please find the difference in below table.

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Features	SQL Server 2016 Enterprise	SQL Server 2016 Standard	SQL Server 2016 Express	SQL Server 2016 Developer
Maximum number of cores	Unlimited	24 Cores	4 Cores	Unlimited
Maximum memory utilized per instance	Operating system max	128 GB	1 GB	Operating system max
Maximum size	524 PB	524 PB	10 GB	524 PB
Production use rights	Yes	Yes	Yes	No
Basic OLTP	Yes	Yes	Yes	Yes
Manageability: Management Studio, policy-based management	Yes	Yes	Yes	Yes
Basic high availability: 2-node single database failover, non-readable secondary	Yes	Yes	NO	Yes
Enterprise data management: Master Data Services, Data Quality Services	Yes	NO	NO	Yes
Advanced OLTP: In-memory OLTP, operational analytics	Yes	NO	NO	Yes
Advanced High Availability: Always On Availability Groups, multi-database failover, readable secondaries	Yes	NO	NO	Yes
Basic security: Row-level security, data masking, basic auditing, separation of duties	Yes	Yes	NO	Yes
Advanced security: Transparent database encryption, Always Encrypted	Yes	No	NO	Yes
Advanced data integration: Fuzzy grouping and lookups, change data capture	Yes	NO	NO	Yes
Data warehousing: In-Memory Columnstore, partitioning	Yes	No	NO	Yes
PolyBase2	Yes	Yes	NO	Yes
Maximum memory utilized per instance of Analysis Services	Operating system max	Tabular: 16 GB MOLAP: 64 GB	No	NO
Maximum memory utilized per instance of Reporting Services	Operating system max	64 GB	Express with Advanced Services: 4 GB	NO
Programmability and developer tools: T-SQL, CLR, Data Types, FileTable, JSON	Yes	Yes	Yes	Yes
Basic reporting and analytics	Yes	Yes	No	Yes
Basic data integration: SQL Server Integration Services, built-in connectors	Yes	Yes	No	Yes
Basic corporate business intelligence: Basic multi-dimensional models, basic tabular model, in-memory storage mode	Yes	Yes	No	Yes
Mobile reports and KPIs	Yes	No	NO	Yes
Advanced corporate business intelligence: Advanced multi-dimensional models, advanced tabular model, DirectQuery storage mode, advanced data mining	Yes	NO	NO	Yes
Basic R integration: Connectivity to R open, limited parallelism	Yes	Yes	Yes	Yes
Advanced R integration: Full parallelism ScaleR	Yes	NO	NO	Yes
Hybrid cloud Stretch Database	Yes	Yes	Yes	Yes

If you have any questions, please comment below, I will try to reply back ASAP

SSMS unable to read/find .bak files or .mdf or .ldf files

Problem:

I have a problem this morning, SQL Server.Bak,.ldf, .mdf files available when I see physically, but SSMS can't read this files from the navigation bar.


Solution:

"The reason it won't "open" the folder is that the service account running the SQL Server Engine service does not have read permission on the folder in question. Assign the windows user group for that SQL Server instance the rights to read and list contents at the WINDOWS level. Then you should see the files that you want to attach inside of the folder."

or else

Change SQL Server Service account to Local windows admin account, your problem will be solved.

How to Use MySQL GRANT to Grant Privileges to Account

MySQL GRANT Statement Syntax:

MySQL provides you with the statement that allows you to grant access privileges to database accounts. MySQL GRANT The following illustrates the GRANT statement syntax:


Privileges:  indicates the privileges that you assign to the account. For example, the privilegeCREATE allows an account to Create Database and Create Tables. You can grant multiple privileges using single statementGRANT; the privileges are separated by commas.

Performance best practices for SQL Server in Azure Virtual Machines

Quick check list

The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

Area	Optimizations
VM size	DS3 or higher for SQL Enterprise edition. DS2 or higher for SQL Standard and Web editions.
Storage	Use Premium Storage. Standard storage is only recommended for dev/test. Keep the storage account and SQL Server VM in the same region. Disable Azure geo-redundant storage (geo-replication) on the storage account.
Disks	Use a minimum of 2 P30 disks (1 for log files; 1 for data files and TempDB). Avoid using operating system or temporary disks for database storage or logging. Enable read caching on the disk(s) hosting the data files and TempDB. Do not enable caching on disk(s) hosting the log file. Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk. Stripe multiple Azure data disks to get increased IO throughput. Format with documented allocation sizes.
I/O	Enable database page compression. Enable instant file initialization for data files. Limit or disable autogrow on the database. Disable autoshrink on the database. Move all databases to data disks, including system databases. Move SQL Server error log and trace file directories to data disks. Setup default backup and database file locations. Enable locked pages. Apply SQL Server performance fixes.

Go to here for more details