Just a day ago, I was looking for script which generates all the tables in database along with its schema name. I tried to Search@SQLAuthority.com but got too many results. For the same reason, I am going to write down today’s quick and small blog post and I will remember that I had written I wrote it after my 1000th article.
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
Reference :Â Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
32 Comments. Leave new
Hi Pinal,
Thanks for the blog. I have a question. I would like to get the table name and locks for that particular table. The current locks by a particular user can be found out in sys.dm_exec_sessions
Do you have any suggests?
Please let me know.
Thanks
Murali.
This will work too…
SELECT SCHEMA_NAME,NAME
FROM INFORMATION_SCHEMA.SCHEMATA i
INNER JOIN SYS.TABLES s
ON i.SCHEMA_NAME = SCHEMA_NAME(s.SCHEMA_ID)
ORDER BY SCHEMA_NAME
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
The above statements will give the same information using single statements
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
Hi,
I have created a linked server named like Test. It contains around 20 tables.
I want to list out tables in linked server in sql server. Can you please let me know how to do?
Thanks,
Murali
I am running something similar (see below). I use this to help me find what tables a field is in, however I noticed that a field/table combo that exists in the database does NOT appear when I run this, why is that? I also just looked for the table in sys.table and it doesn’t appear, it is a custom table, could that be the issue, do you have to run something to get tables to appear in sys.table?
SELECT
t.name AS table_name
,SCHEMA_NAME(schema_id) AS schema_name
,c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘insert field name’
ORDER BY schema_name, table_name;
Thanks everyone!
Jill
Is there any way i can know who created Schema, in my Db someone created Custom schema and I want to know who did that >>
Hello, is there any way to select all tables from all databases inside the same server?
Create table #yourcolumndetails(
DBaseName varchar(100),
TableSchema varchar(50),
TableName varchar(100),
ColumnName varchar(100),
DataType varchar(100),
CharMaxLength varchar(100))
EXEC sp_MSForEachDB @command1=’USE [?];
INSERT INTO #yourcolumndetails SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ”origin”’
select * from #yourcolumndetails
Drop table #yourcolumndetails