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) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.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(tbl.name) + ' where ' +
          rtrim(col.name) + ' like %' + rtrim(@text) + '%'
from sysobjects tbl
inner join syscolumns col on tbl.id = col.id
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 sh.name+'.'+o.name AS TableName ,s.name as ColumnName
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name END AS DataType
,CASE
             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 sh.name+'.'+o.name,s.column_id

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

If you want to get the details of all hosts that are connected to a db with the program to which they are interacting :
SELECT
    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,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id

12 comments:

  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

    ReplyDelete
  2. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.

    Android Training in Bangalore

    ReplyDelete
  3. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training India . Nowadays Java has tons of job opportunities on various vertical industry.

    ReplyDelete