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
Some more simple ones , and basic ones indeed
SELECT statement:
Retrieve data from one or more tables based on specified conditions.
Example: SELECT column1, column2 FROM table WHERE condition;
INSERT statement:
Insert new data into a table.
Example: INSERT INTO table (column1, column2) VALUES (value1, value2);
UPDATE statement:
Modify existing data in a table.
Example: UPDATE table SET column1 = value1 WHERE condition;
DELETE statement:
Remove data from a table based on specified conditions.
Example: DELETE FROM table WHERE condition;
JOIN clause:
Combine rows from multiple tables based on a related column.
Example: SELECT column1, column2 FROM table1 JOIN table2 ON table1.column = table2.column;
GROUP BY clause:
Group rows based on a column and perform aggregate functions.
Example: SELECT column, COUNT(*) FROM table GROUP BY column;
ORDER BY clause:
Sort the result set in ascending or descending order.
Example: SELECT column1, column2 FROM table ORDER BY column1 DESC;
HAVING clause:
Filter groups in the result set based on conditions.
Example: SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 10;
Subqueries:
Embed a query within another query to perform complex operations.
Example: SELECT column1 FROM table WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);
UNION operator:
Combine the result sets of multiple SELECT statements into a single result set.
Example: SELECT column1 FROM table1 UNION SELECT column2 FROM table2;
SELECT statement:
Retrieve data from one or more tables based on specified conditions.
Example: SELECT * FROM table_name WHERE condition;
INSERT statement:
Insert new data into a table.
Example: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE statement:
Modify existing data in a table.
Example: UPDATE table_name SET column1 = value1 WHERE condition;
DELETE statement:
Remove data from a table based on specified conditions.
Example: DELETE FROM table_name WHERE condition;
CREATE TABLE statement:
Create a new table in the database.
Example: CREATE TABLE table_name (column1 datatype1, column2 datatype2);
ALTER TABLE statement:
Modify the structure of an existing table.
Example: ALTER TABLE table_name ADD column datatype;
DROP TABLE statement:
Delete a table from the database.
Example: DROP TABLE table_name;
CREATE INDEX statement:
Create an index on a table column to improve query performance.
Example: CREATE INDEX index_name ON table_name (column);
SELECT DISTINCT statement:
Retrieve unique values from a column in a table.
Example: SELECT DISTINCT column FROM table_name;
ORDER BY clause:
Sort the result set in ascending or descending order.
Example: SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
GROUP BY clause:
Group rows based on a column and perform aggregate functions.
Example: SELECT column, COUNT(*) FROM table_name GROUP BY column;
JOIN clause:
Combine rows from multiple tables based on a related column.
Example: SELECT column1, column2 FROM table1 JOIN table2 ON table1.column = table2.column;
Subqueries:
Embed a query within another query to perform complex operations.
Example: SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);
LIMIT clause:
Limit the number of rows returned in a query result.
Example: SELECT column FROM table_name LIMIT 10;
TRUNCATE TABLE statement:
Remove all data from a table, but keep the table structure.
Example: TRUNCATE TABLE table_name;
In SQL, joins are used to combine rows from different tables based on a related column between them. There are various types of joins available, each serving a different purpose. Here are the most common types of joins:
INNER JOIN:
Returns only the matching rows from both tables.
Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
LEFT JOIN (or LEFT OUTER JOIN):
Returns all the rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for the right table.
Syntax: SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
RIGHT JOIN (or RIGHT OUTER JOIN):
Returns all the rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for the left table.
Syntax: SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
FULL JOIN (or FULL OUTER JOIN):
Returns all the rows from both tables. If no match is found, NULL values are returned for the non-matching side.
Syntax: SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;
CROSS JOIN:
Returns the Cartesian product of both tables, i.e., all possible combinations of rows from both tables.
Syntax: SELECT * FROM table1 CROSS JOIN table2;
SELF JOIN:
Joins a table with itself, treating it as two separate tables.
Syntax: SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column = t2.column;
NATURAL JOIN:
Automatically matches the columns with the same name in both tables.
Syntax: SELECT * FROM table1 NATURAL JOIN table2;
It's important to specify the join condition using the ON keyword, indicating the columns that are used to match the rows. Understanding the differences and use cases for each type of join allows you to retrieve and combine data effectively from multiple tables in SQL.
In this example, we define a CTE named sales_total that calculates the total sales for each product from the sales table. Then, we use the CTE to select the product names and total sales where the total sales amount is greater than 10,000.
WITH sales_total (product_name, total_sales) AS (
SELECT product_name, SUM(sales_amount)
FROM sales
GROUP BY product_name
)
SELECT * FROM sales_total WHERE total_sales > 10000;
CTEs offer the following benefits:
Readability: CTEs provide a way to break down complex queries into smaller, more manageable parts, making the overall query easier to read and understand.
Reusability: CTEs can be referenced multiple times within the same query, allowing you to reuse the same result set in different parts of the query.
Recursive Queries: CTEs can be used to create recursive queries, which are queries that refer back to themselves. This is particularly useful for tasks such as hierarchical data structures or graph traversals.
Performance Optimization: Depending on the database engine, CTEs can be optimized by the query optimizer, potentially improving query execution performance.
Overall, CTEs provide a powerful tool for organizing and simplifying complex queries, improving code readability, and enabling recursive operations in SQL.
Post a Comment