Most useful sql queries

To get rowcount of each table within a particular database along with some other attributes like schema name :

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, AS table_name, i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = AND i.indid < 2 order by i.rows desc

To get table having presence of a particular text
declare @text varchar(255)
set @text = 'Vip'
select 'select * from ' + rtrim( + ' where ' +
          rtrim( + ' like %' + rtrim(@text) + '%'
from sysobjects tbl
inner join syscolumns col on =
and col.xtype in (167, 175, 231, 239) -- (n)char and (n)varchar, there may be others to include
and col.length > 30 -- arbitrary min length into which you might store a URL
where tbl.type = 'U'

To get all tables within a database along with each column in each table , its datatype and IsNUll attribute :

SELECT'.' AS TableName , as ColumnName
             WHEN IN ('char','varchar') THEN'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN IN ('nvarchar','nchar') THEN'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN IN ('numeric') THEN'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE END AS DataType
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL' END AS Nullable
    FROM sys.columns  s
        INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id
        INNER JOIN sys.objects                 o ON s.object_id=o.object_id and o.type_desc = 'USER_TABLE'
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    ORDER BY'.',s.column_id

If you want to import data from excel using OpenRowset and sp_configure throws error:
sp_configure 'show advanced options', 1;
GO  --Added      
sp_configure 'Ad Hoc Distributed Queries', 1;

If you want to get the details of all hosts that are connected to a db with the program to which they are interacting :
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id

1 comment:

  1. The list of SQL queries shared are very much useful and helpful My sincere thanks for sharing this post
    Dot Net Training in Chennai